--处理示例--要处理的数据 create table tb(id int identity(1,1),contents ntext) insert tb select replicate('a,a',8000) go --拆分处理--为了效率,所以要一个辅助表配合 select top 4000 id=identity(int,1,1) into 序数表 from syscolumns a,syscolumns b alter table 序数表 add constraint pk_id_序数表 primary key(id)--保存拆分结果的临时表 create table #t(id int identity(1,1),re nvarchar(50)) declare @s nvarchar(4000),@i int,@j intselect @s=substring(contents,1,4000),@i=1 from tb where id=1 while @s<>'' begin if len(@s)=4000 select @j=4000-charindex(',',reverse(@s)) ,@i=@i+@j+1 ,@s=left(@s,@j) else select @i=@i+4000,@j=len(@s) insert #t select substring(@s,id,charindex(',',@s+',',id)-id) from 序数表 where id<=@j+1 and charindex(',',','+@s,id)-id=0 select @s=substring(contents,@i,4000) from tb where id=1 end select * from #t go--删除测试 drop table tb,序数表,#t
create table tb(id int identity(1,1),contents ntext)
insert tb select replicate('a,a',8000)
go
--拆分处理--为了效率,所以要一个辅助表配合
select top 4000 id=identity(int,1,1) into 序数表
from syscolumns a,syscolumns b
alter table 序数表 add constraint pk_id_序数表 primary key(id)--保存拆分结果的临时表
create table #t(id int identity(1,1),re nvarchar(50))
declare @s nvarchar(4000),@i int,@j intselect @s=substring(contents,1,4000),@i=1 from tb where id=1
while @s<>''
begin
if len(@s)=4000
select @j=4000-charindex(',',reverse(@s))
,@i=@i+@j+1
,@s=left(@s,@j)
else
select @i=@i+4000,@j=len(@s)
insert #t select substring(@s,id,charindex(',',@s+',',id)-id)
from 序数表
where id<=@j+1 and charindex(',',','+@s,id)-id=0
select @s=substring(contents,@i,4000) from tb where id=1
end
select * from #t
go--删除测试
drop table tb,序数表,#t