SQL 中 超简单的模拟split

--> 测试数据: #T

if object_id('tempdb.dbo.#T')is not null drop table#T

create table #T (id int,namevarchar(8))

insert into #T

select 1,'jame,job' unionall

select 2,'paul,mc' unionall

select 3,'carl';

--> 1. CTE 递归找分隔字符位置法：速度极快

with T (id,P1,P2) as

(

    selectid,charindex(',',','+name),charindex(',',name+',')+1from #T

    unionall

    selecta.id,b.P2,charindex(',',name+',',b.P2)+1from #T a join T b ona.id=b.id where charindex(',',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 orderby 1