declare @T table(Name nvarchar(70))declare @s nvarchar(150) set @s=replicate('a',71)--测试while len(@s)>70 begin insert @T select left(@s,70) set @s=stuff(@s,1,70,'') end insert @T values(@s)select * from @T go declare @T table(Name nvarchar(70))declare @s nvarchar(150) set @s=replicate('a',150)--测试150while len(@s)>70 begin insert @T select left(@s,70) set @s=stuff(@s,1,70,'') end insert @T values(@s) select * from @T
create table tb(Name nvarchar(171))declare @s nvarchar(4000) set @s=replicate('a',2000)--测试 declare @i int set @I = len(@s) /70 print @i while @i > 0 begin set @s=stuff(@s,@i*70+1,0,''' union all select ''') set @i = @I - 1 endexec( 'insert tb select '''+@s+'''') select *,len(name) from tbdrop table tb
create procedure sp_weifa @tel varchar(21), @spid varchar(21), @plateid varchar(20), @linkid varchar(50) as begin declare @str1 varchar(1000),@str2 varchar(1000),@cnt int Select @str2='您的车最近一周没有违章,请您安全驾驶!',@str1='',@cnt=0 --按车牌号查询 select @cnt=@cnt+1, @str1=@str1+','+substring(rtrim(inspedate),6,2)+'月'+substring(rtrim(inspedate),9,2)+'日在'+' '+inspeadre+' '+vehstatue from weifa where plateid=@plateid and DateDiff(day,getdate(),rtrim(inspedate))>=-7 set @str1='您最近一周有'+rtrim(@cnt)+'次违章'+@str1 If @@Rowcount<>1 begin insert into yni_mt(tel,spid,linkid,servicetype,feetype,feevalue,reportflag,msg,morelatetomtflag) values(@tel,@spid,@linkid,'JSYXX',2,'200',1,@str2,0) end else begin insert into yni_mt(tel,spid,linkid,servicetype,feetype,feevalue,reportflag,msg,morelatetomtflag) values(@tel,@spid,@linkid,'JSYXX',2,'200',1,@str1,0) end end go 就是把这里面的 @str1 按70个字符拆分
while len(@str1) > 0 begin if len(@str1)>70 begin set @oooo=substrng(@str1,1,70) --插入数据库语句 set @str1= stuff(@str1,1,70,'') end else begin set @oooo= @str1 --插入数据库语句 set @oooo= '' end end
while len(@str1) > 0 begin if len(@str1)>=70 begin set @oooo=substrng(@str1,1,70) --插入数据库语句 set @str1= stuff(@str1,1,70,'')--删除前面用过的 end else begin set @oooo= @str1 --插入数据库语句 set @str1= '' end end
SET ANSI_NULLS ON GO SET QUOTED_IDENTIFIER ON GO -- ============================================= -- Author: <Author,,Name> -- Create date: <Create Date,,> -- Description: <Description,,> -- test1 -- ============================================= alter PROCEDURE test1 AS BEGIN declare @s varchar(1000),@len int,@nowcount int declare @t table(namestr varchar(100)) set @s = '这个地方放你想分离的字符串' set @len=len(@s) set @nowcount=1 while @nowcount*70<@len begin insert @t select substring(@s,(@nowcount-1)*70,@nowcount*70) set @nowcount = @nowcount+1 end insert @t select substring(@s,(@nowcount-1)*70,@len-(@nowcount-1)*70) select * from @t END GO
substrng(@str1,1,70) 取前70个字符
substring(@str1,71,70)取71-140字符
substring(@str1,141,70) 取140后的字符不管单字节还是双字节字符都当是一个字符
set ...
else
set...
set @s=replicate('a',71)--测试while len(@s)>70
begin
insert @T select left(@s,70)
set @s=stuff(@s,1,70,'')
end
insert @T values(@s)select * from @T
go
declare @T table(Name nvarchar(70))declare @s nvarchar(150)
set @s=replicate('a',150)--测试150while len(@s)>70
begin
insert @T select left(@s,70)
set @s=stuff(@s,1,70,'')
end
insert @T values(@s)
select * from @T
求知者
等 级:
发表于:2007-12-08 18:17:525楼 得分:0
都是笨方法,有好点的吗?假如有700个字符还的判断10次吗?
---------------------------------------想法天真,如果软件能跟上人的思维,还要人干嘛
要找原因,从数据库的设计就有问题...
set @s=replicate('a',2000)--测试
declare @i int
set @I = len(@s) /70 print @i
while @i > 0
begin
set @s=stuff(@s,@i*70+1,0,''' union all select ''')
set @i = @I - 1
endexec( 'insert tb select '''+@s+'''')
select *,len(name) from tbdrop table tb
@tel varchar(21),
@spid varchar(21),
@plateid varchar(20),
@linkid varchar(50)
as
begin
declare @str1 varchar(1000),@str2 varchar(1000),@cnt int
Select @str2='您的车最近一周没有违章,请您安全驾驶!',@str1='',@cnt=0
--按车牌号查询
select
@cnt=@cnt+1,
@str1=@str1+','+substring(rtrim(inspedate),6,2)+'月'+substring(rtrim(inspedate),9,2)+'日在'+' '+inspeadre+' '+vehstatue
from
weifa
where
plateid=@plateid
and
DateDiff(day,getdate(),rtrim(inspedate))>=-7
set @str1='您最近一周有'+rtrim(@cnt)+'次违章'+@str1
If @@Rowcount<>1
begin
insert into yni_mt(tel,spid,linkid,servicetype,feetype,feevalue,reportflag,msg,morelatetomtflag)
values(@tel,@spid,@linkid,'JSYXX',2,'200',1,@str2,0)
end
else
begin
insert into yni_mt(tel,spid,linkid,servicetype,feetype,feevalue,reportflag,msg,morelatetomtflag)
values(@tel,@spid,@linkid,'JSYXX',2,'200',1,@str1,0)
end
end
go
就是把这里面的 @str1 按70个字符拆分
begin
if len(@str1)>70
begin
set @oooo=substrng(@str1,1,70)
--插入数据库语句
set @str1= stuff(@str1,1,70,'')
end
else
begin
set @oooo= @str1
--插入数据库语句
set @oooo= ''
end
end
while len(@str1) > 0
begin
if len(@str1)>=70
begin
set @oooo=substrng(@str1,1,70)
--插入数据库语句
set @str1= stuff(@str1,1,70,'')--删除前面用过的
end
else
begin
set @oooo= @str1
--插入数据库语句
set @str1= ''
end
end
GO
SET QUOTED_IDENTIFIER ON
GO
-- =============================================
-- Author: <Author,,Name>
-- Create date: <Create Date,,>
-- Description: <Description,,>
-- test1
-- =============================================
alter PROCEDURE test1
AS
BEGIN
declare @s varchar(1000),@len int,@nowcount int
declare @t table(namestr varchar(100))
set @s = '这个地方放你想分离的字符串'
set @len=len(@s)
set @nowcount=1
while @nowcount*70<@len
begin
insert @t select substring(@s,(@nowcount-1)*70,@nowcount*70)
set @nowcount = @nowcount+1
end
insert @t select substring(@s,(@nowcount-1)*70,@len-(@nowcount-1)*70)
select * from @t
END
GO