Ntext字段拆分处理

<script type="text/javascript"> </script> <script type="text/javascript" src="http://pagead2.googlesyndication.com/pagead/show_ads.js"> </script>

<script type="text/javascript"> </script><script type="text/javascript" src="http://pagead2.googlesyndication.com/pagead/show_ads.js"> </script>

ifexists(select*fromdbo.sysobjectswhereid=object_id(N'[dbo].[p_split]')andOBJECTPROPERTY(id,N'IsProcedure')=1)dropprocedure[dbo].[p_split]GO

/*-- Ntext 字段拆分处理

 按指定的分隔符,将 Ntext字段拆分成多条记录 注意:处理过程固定以名为id的int型字段为主键 如果不满足这个条件,则要对应的修改存储过程

--邹建2004.07--*/

/*--调用示例

 --测试数据 createtabletb(idintidentity(1,1),content Ntext) inserttbselect'001,002' unionallselect'001,002,003,004,005,006,007,008,009,010' unionallselectreplicate('001,002,003,004,005,006,007,008,009,010',8000)

  --调用存储过程,进行拆分 execp_split'tb','content',',','id=3'

 droptabletb--*/

createprocp_split@tbnamesysname,  --要处理的表名@fdnamesysname,  --text/ Ntext字段名@splitcharnvarchar(10)=',',--拆分的字符串分隔符@wherenvarchar(1000)=''--要处理的记录的条件asifisnull(@splitchar,'')=''set@splitchar=','

declare@snvarchar(4000)set@s='createtable#t(idintidentity(1,1),renvarchar(50))declare@idint,@ptrvarbinary(16)declare@snvarchar(4000),@iint,@jintdeclare@sp1varchar(10),@stepint

select@sp1=reverse(@splitchar),@step=len(@splitchar)

declaretbcursorlocalforselectid,s=substring(['+@fdname+'],1,4000)from['+@tbname+']wheredatalength(['+@fdname+'])>0 '+caseisnull(@where,'')when''then'' else'and('+@where+')'end+'

opentbfetchtbinto@id,@swhile@@fetch_status=0begin set@i=1 while@s<>'''' begin  iflen(@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#tselectsubstring(@s,id,charindex(@splitchar,@s+@splitchar,id)-id)  from序数表  whereid<=@j+@stepandcharindex(@splitchar,@splitchar+@s,id)-id=0  select@s=substring(['+@fdname+'],@i,4000)  from['+@tbname+']  whereid=@id end

 fetchtbinto@id,@sendclosetbdeallocatetbselect*from#t'execsp_executesql@s ,N'@splitcharnvarchar(10)' ,@splitchargo
<script type="text/javascript"> </script> <script type="text/javascript" src="http://pagead2.googlesyndication.com/pagead/show_ads.js"> </script>

<script type="text/javascript"> </script><script type="text/javascript" src="http://pagead2.googlesyndication.com/pagead/show_ads.js"> </script>