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 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
--*/
1create proc p_split
2@tbname sysname, --要处理的表名
3@fdname sysname, --text/ntext字段名
4@splitchar nvarchar(10)=',',--拆分的字符串分隔符
5@where nvarchar(1000)=''--要处理的记录的条件
6as
7if isnull(@splitchar,'')='' set @splitchar=','
8
9declare @s nvarchar(4000)
10set @s='
11create table #t(id int identity(1,1),re nvarchar(50))
12declare @id int,@ptr varbinary(16)
13declare @s nvarchar(4000),@i int,@j int
14declare @sp1 varchar(10),@step int
15
16select @sp1=reverse(@splitchar),@step=len(@splitchar)
17
18declare tb cursor local for
19select id,s=substring(['+@fdname+'],1,4000)
20from ['+@tbname+']
21where datalength(['+@fdname+'])>0
22'+case isnull(@where,'') when '' then ''
23 else ' and('+@where+')' end+'
24
25open tb
26fetch tb into @id,@s
27while @@fetch_status=0
28begin
29 set @i=1
30 while @s<>''''
31 begin
32 if len(@s)=4000
33 select @j=4000-charindex(@sp1,reverse(@s))
34 ,@i=@i+@j+@step
35 ,@s=left(@s,@j)
36 else
37 select @i=@i+4000,@j=len(@s)
38 insert #t select substring(@s,id,charindex(@splitchar,@s+@splitchar,id)-id)
39 from 序数表
40 where id<=@j+@step and charindex(@splitchar,@splitchar+@s,id)-id=0
41 select @s=substring(['+@fdname+'],@i,4000)
42 from ['+@tbname+']
43 where id=@id
44 end
45
46 fetch tb into @id,@s
47end
48close tb
49deallocate tb
50select * from #t
51'
52exec sp_executesql @s
53 ,N'@splitchar nvarchar(10)'
54 ,@splitchar
55go
2@tbname sysname, --要处理的表名
3@fdname sysname, --text/ntext字段名
4@splitchar nvarchar(10)=',',--拆分的字符串分隔符
5@where nvarchar(1000)=''--要处理的记录的条件
6as
7if isnull(@splitchar,'')='' set @splitchar=','
8
9declare @s nvarchar(4000)
10set @s='
11create table #t(id int identity(1,1),re nvarchar(50))
12declare @id int,@ptr varbinary(16)
13declare @s nvarchar(4000),@i int,@j int
14declare @sp1 varchar(10),@step int
15
16select @sp1=reverse(@splitchar),@step=len(@splitchar)
17
18declare tb cursor local for
19select id,s=substring(['+@fdname+'],1,4000)
20from ['+@tbname+']
21where datalength(['+@fdname+'])>0
22'+case isnull(@where,'') when '' then ''
23 else ' and('+@where+')' end+'
24
25open tb
26fetch tb into @id,@s
27while @@fetch_status=0
28begin
29 set @i=1
30 while @s<>''''
31 begin
32 if len(@s)=4000
33 select @j=4000-charindex(@sp1,reverse(@s))
34 ,@i=@i+@j+@step
35 ,@s=left(@s,@j)
36 else
37 select @i=@i+4000,@j=len(@s)
38 insert #t select substring(@s,id,charindex(@splitchar,@s+@splitchar,id)-id)
39 from 序数表
40 where id<=@j+@step and charindex(@splitchar,@splitchar+@s,id)-id=0
41 select @s=substring(['+@fdname+'],@i,4000)
42 from ['+@tbname+']
43 where id=@id
44 end
45
46 fetch tb into @id,@s
47end
48close tb
49deallocate tb
50select * from #t
51'
52exec sp_executesql @s
53 ,N'@splitchar nvarchar(10)'
54 ,@splitchar
55go
1create proc p_split@tbname sysname, --要处理的
2表名@fdname sysname, --text/ntext字段名
3@splitchar nvarchar(10)=',',--拆分的字符串分隔符
4@where nvarchar(1000)=''--要处理的记录的条件asif
5isnull(@splitchar,'')='' set @splitchar=','
6
7declare @s nvarchar(4000)set @s='create
8table #t(id int identity(1,1),re nvarchar(50))
9declare @id int,@ptr varbinary(16)
10declare @s nvarchar(4000),@i int,@j
11intdeclare @sp1 varchar(10),@step int
12
13select @sp1=reverse(@splitchar),@step=len(@splitchar)
14
15declare tb cursor local for
16select id,s=substring(['+@fdname+'],1,4000)
17from ['+@tbname+']where datalength(['+@fdname+'])>0 '+case isnull(@where,'')
18when '' then '' else ' and('+@where+')' end+'
19
20open tb fetch tb into @id,@swhile
21@@fetch_status=0begin set @i=1 while @s<>''''
22begin
23 if len(@s)=4000
24 select @j=4000-charindex(@sp1,reverse(@s)) ,
25 @i=@i+@j+@step,
26 @s=left(@s,@j)
27 else
28 select @i=@i+4000,@j=len(@s)
29 insert #t select substring(@s,id,charindex(@splitchar,@s+@splitchar,id)-id)
30 from 序数表 where id<=@j+@step and charindex(@splitchar,@splitchar+@s,id)-id=0
31 select @s=substring(['+@fdname+'],@i,4000) from ['+@tbname+']
32where id=@id
33end
34
35 fetch tb into @id,@sendclose tbdeallocate
36tbselect * from #t'
37exec sp_executesql @s ,N'@splitchar nvarchar(10)' ,@splitchargo
38
2表名@fdname sysname, --text/ntext字段名
3@splitchar nvarchar(10)=',',--拆分的字符串分隔符
4@where nvarchar(1000)=''--要处理的记录的条件asif
5isnull(@splitchar,'')='' set @splitchar=','
6
7declare @s nvarchar(4000)set @s='create
8table #t(id int identity(1,1),re nvarchar(50))
9declare @id int,@ptr varbinary(16)
10declare @s nvarchar(4000),@i int,@j
11intdeclare @sp1 varchar(10),@step int
12
13select @sp1=reverse(@splitchar),@step=len(@splitchar)
14
15declare tb cursor local for
16select id,s=substring(['+@fdname+'],1,4000)
17from ['+@tbname+']where datalength(['+@fdname+'])>0 '+case isnull(@where,'')
18when '' then '' else ' and('+@where+')' end+'
19
20open tb fetch tb into @id,@swhile
21@@fetch_status=0begin set @i=1 while @s<>''''
22begin
23 if len(@s)=4000
24 select @j=4000-charindex(@sp1,reverse(@s)) ,
25 @i=@i+@j+@step,
26 @s=left(@s,@j)
27 else
28 select @i=@i+4000,@j=len(@s)
29 insert #t select substring(@s,id,charindex(@splitchar,@s+@splitchar,id)-id)
30 from 序数表 where id<=@j+@step and charindex(@splitchar,@splitchar+@s,id)-id=0
31 select @s=substring(['+@fdname+'],@i,4000) from ['+@tbname+']
32where id=@id
33end
34
35 fetch tb into @id,@sendclose tbdeallocate
36tbselect * from #t'
37exec sp_executesql @s ,N'@splitchar nvarchar(10)' ,@splitchargo
38