拆分字段

拆分字段与合并字段是字符串处理常见的两个问题，下面将针对实例进行分析求解。
问题：將字符串拆分成記錄集
原表Table1(id int identity(1,1),name varchar(100))
id           name
-------------------
1            'a,b,c'
2            'e,f'

希望得到结果：
id    name
-----------
1     'a'
1     'b'
1     'c'
2     'e'
2     'f'

一、首先我们利用最原始的方法进行求解，即通过逐条循环来实现。
create table #a(id int identity(1,1),name varchar(100))
insert #a(name)
select 'a,b,c'
union all select 'd,e,f'
union all select 'g,h'
1.利用游标
DECLARE @id int,@name varchar(100)
create table #b(id int,name varchar(100))
DECLARE a_cursor CURSOR FOR
SELECT id, name
FROM #a
ORDER BY id
 
OPEN a_cursor
 
FETCH NEXT FROM a_cursor
INTO @id, @name

WHILE @@FETCH_STATUS = 0
BEGIN
  while charindex(',',@name)>0
    begin
 insert #b(id,name)
 select @id,left(@name,charindex(',',@name)-1)
 set @name=right(@name,len(@name)-charindex(',',@name))
    end
  insert #b(id,name)
  select @id,@name
  FETCH NEXT FROM a_cursor
  INTO @id, @name
end
CLOSE a_cursor
DEALLOCATE a_cursor

select *from #b
drop table #b
2.利用临时表代替游标
DECLARE @id int,@name varchar(100),@count int
create table #tmp(no int identity(1,1),id int,name varchar(100))
create table #b(id int,name varchar(100))
insert #tmp(id,name)
select * from #a
set @count=@@rowcount
while @count>0
begin
  select @id=id,@name=name from #tmp where no=@count
  while charindex(',',@name)>0
    begin
 insert #b(id,name)
 select @id,left(@name,charindex(',',@name)-1)
 set @name=right(@name,len(@name)-charindex(',',@name))
    end
  insert #b(id,name)
  select @id,@name
  set @count=@count-1
end

select * from #b order by id,name
drop table #b,#tmp
上述两种方法都两次利用了循环，清晰易懂，但代码长而且速度慢。

二、利用replace函数与动态语句结合。
-- test:
create table #a(id int identity(1,1),name varchar(100))
create table #b(id int,name varchar(100))
insert #a(name)
select 'a,b,c'
union all select 'd,e,f'
union all select 'g,h'
go
declare @sql varchar(8000)
set @sql = ''
select @sql = @sql + ' insert into #b select ' + cast(id as varchar) + ',''' + replace(name,',',''' union all select ' + cast(id as varchar) + ',''') + '''' from #a
--print @sql
exec (@sql)
go
select * from #b
go
drop table #a,#b

解析：此段代码很简洁，但使用了比较复杂的动态语句与代替函数的使用技巧，给程序的解读带来一定的困难，而且受字段串长度的限制，
一旦超过8000个字符就不能满足要求。

三、利用substring函数计算出每一段分割符的上下位置，从而达到分离字段的目的。
此方法可以处理较大数据量的要求而且速度较快，但此方法比较巧妙，难以联想到。
create table #a(id int identity(1,1),name varchar(100))

insert #a(name)
select 'a,b,c'
union all select 'd,e,f'
union all select 'g,h'

select a.id,name=substring(a.name,b.id,charindex(',',a.name+',',b.id)-b.id)
from #a a join(
 select top 100 id=a.id+b.id+1
 from(
  select id=0 union all select 1
  union all select id=2 union all select 3
  union all select id=4 union all select 5
  union all select id=6 union all select 7
  union all select id=8 union all select 9
 ) a,(
  select id=0 union all select 10
  union all select id=20 union all select 30
  union all select id=40 union all select 50
  union all select id=60 union all select 70
  union all select id=80 union all select 90
 ) b
)b on substring(','+a.name,b.id,1)=','

drop table #a
综合起来，上述三种方法相比：
第一种通俗易懂，符合一般人的编写思想，但效率不高。
第二种方法语句短小精悍，较率也较高，但由于受字符串长度限制，通用性不好，难以处理大批量的数据。
第三种方法弥补了第二种方法的不足，但同时加进了程序的复杂度，不是很容易想得到，想得通。

四、 Ntext字段的拆分

if   exists   (select   *   from   dbo.sysobjects   where   id   =   object_id(N'[dbo].[f_GetStr]')   and   xtype   in   (N'FN',   N'IF',   N'TF'))  
  drop   function   [dbo].[f_GetStr]  
  GO  
   
  /*--得到字符串列表指定位置的字符  
   
  可以自定义字符串列表的分隔符  
  如果取数位置超出的范围,返回空字符串  
   
  --邹建   2005.04(引用请保留此信息)--*/  
   
  /*--调用示例  
  --测试数据  
  declare   @UserItem   table(UserItemID   int,CateName   varchar(50),OtherDemo   text)  
  insert   @UserItem  
  select   1,'张三','50&黄石市小河镇&0762-2262626'   union   all  
  select   2,'李四','35&广州市&020-2262626'   union   all  
  select   3,'博士','25&青岛&0456-2262626'   union   all  
  select   4,'学士','25&北京&010-2262626'  
   
  --分拆  
  select   UserItemID,CateName  
  ,年龄=dbo.f_GetStr(OtherDemo,1)  
  ,地址=dbo.f_GetStr(OtherDemo,2)  
  ,联系电话=dbo.f_GetStr(OtherDemo,3)  
  from   @UserItem  
   
  /*--结果  
   
  UserItemID     CateName           年龄                 地址                     联系电话  
  -----------   ------------   ----------   -------------   ---------------  
  1                       张三                   50                   黄石市小河镇       0762-2262626  
  2                       李四                   35                   广州市                   020-2262626  
  3                       博士                   25                   青岛                       0456-2262626  
  4                       学士                   25                   北京                       010-2262626  
   
  （所影响的行数为   4   行）  
  --*/  
  --*/  
  CREATE   FUNCTION   f_GetStr(  
  @s   ntext,             --字符串列表  
  @pos   int               --要获取的数据信息段  
  )RETURNS   nvarchar(4000)  
  AS  
  BEGIN  
  IF   @s   IS   NULL   RETURN(NULL)  
  DECLARE   @s1   nvarchar(4000),@i   int,@j   int  
  SELECT   @i=1,@j=PATINDEX('%&%',@s)  
  WHILE   @pos>1   AND   @j>0  
  SELECT   @pos=@pos-1,  
  @j=PATINDEX('%&%',SUBSTRING(@s,@i,4000))  
  ,@i=@i+@j  
  RETURN(SUBSTRING(@s,@i,PATINDEX('%&%',SUBSTRING(@s,@i,4000)+'&')-1))  
  END  
  GO