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型字段為主鍵 如果不滿足這個(gè)條件,則要對(duì)應(yīng)的修改存儲(chǔ)過程

--鄒建 2004.07--*/

/*--調(diào)用示例

 --測(cè)試數(shù)據(jù) 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)

  --調(diào)用存儲(chǔ)過程,進(jìn)行拆分 exec p_split 'tb','content',',','id=3'

 drop table tb--*/

create proc [email protected] 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(['[email protected]+'],1,4000)from ['[email protected]+']where datalength(['[email protected]+'])>0 '+case isnull(@where,'') when '' then ''  else ' and('[email protected]+')' 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))    ,@[email protected][email protected][email protected]    ,@s=left(@s,@j)  else    select @[email protected]+4000,@j=len(@s)  insert #t select substring(@s,id,charindex(@splitchar,@[email protected],id)-id)  from 序數(shù)表  where id<[email protected][email protected] and charindex(@splitchar,@[email protected],id)-id=0  select @s=substring(['[email protected]+'],@i,4000)  from ['[email protected]+']  where [email protected] end

 fetch tb into @id,@sendclose tbdeallocate tbselect * from #t'exec sp_executesql @s ,n'@splitchar nvarchar(10)' ,@splitchargo