SQL Server 字符串拆分

已知表格为
id   name
1  jame，job
2  carl，mc
3  paul
拆分为
id  name
1   jame
2   job
3   carl
4   mc
5   paul

----------------------------------------------------------------
--使用循环截取法
declare@tbtable(id intidentity(1,1),name char(50))
insert@tbselect'jame,job'
insert@tbselect'carl,mc'
insert@tbselect'paul'

declare c1 cursorforselect name from@tb
declare@tmptable(id intidentity(1,1),name char(50))
declare@schar(50)
open c1
fetch c1 into@s

while(@@fetch_status=0)
begin  
   whilecharindex(',',@s)>0
      begin
         insertinto@tmp
        values(left(@s,charindex(',',@s)-1))
         set@s=stuff(@s,1,charindex(',',@s),'')
      end
INSERT@tmpVALUES(@s)
fetch  nextfrom c1 into@s
end
select*from@tmp
close c1
deallocate c1
----------------------------------------------------------------
--使用动态SQL语句
declare@tbtable(id intidentity(1,1),name char(50))
insert@tbselect'jame,job'
insert@tbselect'carl,mc'
insert@tbselect'paul'

declare c1 cursorforselect name from@tb
declare@tmptable(id intidentity(1,1),name char(50))
declare@schar(50)
declare@sqlchar(8000)
open c1
fetch c1 into@s

while(@@fetch_status=0)
begin
  set@sql='select '''+replace(@s,',',''' union all select ''')+''''
   insertinto tmp --tmp只能是基本表，必须先定义好tmp的结构
     exec(@sql)
  fetch  nextfrom c1 into@s
end
select*from@tmp
close c1
deallocate c1

--> 测试数据: #T
ifobject_id('tempdb.dbo.#T') isnotnulldroptable #T
createtable #T (id int,name varchar(8))
insertinto #T
select1,'jame,job'unionall
select2,'paul,mc'unionall
select3,'carl';

--> 1. CTE 递归找分隔字符位置法：速度极快
with T (id,P1,P2) as
(
    select id,charindex(',',','+name),charindex(',',name+',')+1from #T
    unionall
    select a.id,b.P2,charindex(',',name+',',b.P2)+1from #T a join T b on a.id=b.id wherecharindex(',',name+',',b.P2)>0
)
select a.id,name=substring(a.name+',',b.P1,b.P2 - b.P1 -1) from #T a join T b on a.id=b.id orderby1
/*
id          name
----------- ---------
1           jame
1           job
2           mc
2           paul
3           carl
*/
--> 测试数据: #T
ifobject_id('tempdb.dbo.#T') isnotnulldroptable #T
createtable #T (id int,name varchar(8))
insertinto #T
select1,'jame,job'unionall
select2,'paul,mc'unionall
select3,'carl';

--> 2. 临时表法：速度比CTE方法相差无几
ifobject_id('tempdb.dbo.#') isnotnulldroptable #
selecttop8000 id=identity(int,1,1) into # from syscolumns,sysobjects --> select max(len(name)) from #T=11
select a.id,name=substring(a.name+',',b.id,charindex(',',a.name+',',b.id+1)-b.id) from #T a innerjoin # b onsubstring(','+a.name,b.id,1)=','
/*
id          name
----------- ---------
1           jame
1           job
2           mc
2           paul
3           carl
*/

3. XML法：速度较慢
select
    a.id,b.name
from
    (select id,name=convert(xml,'<root><v>'+replace(name,',','</v><v>')+'</v></root>') from #T ) a
outer apply
    (select name=N.v.value('.','varchar(100)') from a.name.nodes('/root/v') N(v)) b
/*
id          name
----------- ---------
1           jame
1           job
2           mc
2           paul
3           carl
*/