if exists (select * from dbo.sysobjects where id = object_id(N@#[dbo].[p_split]@#) and OBJECTPROPERTY(id, N@#IsProcedure@#) = 1)drop procedure [dbo].[p_split]GO
/*--Ntext字段拆分处理
按指定的分隔符,将 ntext 字段拆分成多条记录 注意:处理过程固定以名为id的int型字段为主键 如果不满足这个条件,则要对应的修改存储过程
--邹建 2004.07--*/
/*--调用示例
--测试数据 create table tb(id int identity(1,1),content ntext) insert tb select @#001,002@# union all select @#001,002,003,004,005,006,007,008,009,010@# union all select replicate(@#001,002,003,004,005,006,007,008,009,010@#,8000)
--调用存储过程,进行拆分 exec p_split @#tb@#,@#content@#,@#,@#,@#id=3@#
drop table tb--*/
create proc p_split@tbname sysname, --要处理的表名@fdname sysname, --text/ntext字段名@splitchar nvarchar(10)=@#,@#,--拆分的字符串分隔符@where nvarchar(1000)=@#@#--要处理的记录的条件asif isnull(@splitchar,@#@#)=@#@# set @splitchar=@#,@#
declare @s nvarchar(4000)set @s=@#create table #t(id int identity(1,1),re nvarchar(50))declare @id int,@ptr varbinary(16)declare @s nvarchar(4000),@i int,@j intdeclare @sp1 varchar(10),@step int
select @sp1=reverse(@splitchar),@step=len(@splitchar)
declare tb cursor local for select id,s=substring([@#+@fdname+@#],1,4000)from [@#+@tbname+@#]where datalength([@#+@fdname+@#])>0 @#+case isnull(@where,@#@#) when @#@# then @#@# else @# and(@#+@where+@#)@# end+@#
open tb fetch tb into @id,@swhile @@fetch_status=0begin set @i=1 while @s<>@#@#@#@# begin if len(@s)=4000 select @j=4000-charindex(@sp1,reverse(@s)) ,@i=@i+@j+@step ,@s=left(@s,@j) else select @i=@i+4000,@j=len(@s) insert #t select substring(@s,id,charindex(@splitchar,@s+@splitchar,id)-id) from 序数表 where id<=@j+@step and charindex(@splitchar,@splitchar+@s,id)-id=0 select @s=substring([@#+@fdname+@#],@i,4000) from [@#+@tbname+@#] where id=@id end
fetch tb into @id,@sendclose tbdeallocate tbselect * from #t@#exec sp_executesql @s ,N@#@splitchar nvarchar(10)@# ,@splitchargo
延伸阅读
文章来源于领测软件测试网 https://www.ltesting.net/
