SQL 分割符问题

declare @col varchar(1000)
set @col='13478908796，12345678，11111'
select substring(@col,number,charindex('，',@col+'，',number+1)-number) from master..spt_values
where type='p' and number<=len(@col) and substring(@col,number,1)<>'，' and substring('，'+@col,number,1)='，'
/*
----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
13478908796
12345678
11111

(3 行受影响)

*/

create table wanglejun(电话号码 varchar(50))

insert into wanglejun select '13478908796,12345678,11111'

-- 拆分为行
select '电话号码'+cast(row_number() over(order by getdate()) as varchar) '电话号码',
substring(a.电话号码,b.number,charindex(',',a.电话号码+',',b.number)-b.number) 'n'
from wanglejun a
inner join master.dbo.spt_values b
on b.[type]='P' and substring(','+a.电话号码,b.number,1)=','

电话号码             n
---------------- -----------------
电话号码1          13478908796
电话号码2          12345678
电话号码3          11111

(3 row(s) affected)


-- 拆分为列
with t as
(select '电话号码'+cast(row_number() over(order by getdate()) as varchar) '电话号码',
substring(a.电话号码,b.number,charindex(',',a.电话号码+',',b.number)-b.number) 'n'
from wanglejun a
inner join master.dbo.spt_values b
on b.[type]='P' and substring(','+a.电话号码,b.number,1)=',')
select [电话号码1], [电话号码2], [电话号码3]
from t
pivot(max(n) for 电话号码 in ([电话号码1], [电话号码2], [电话号码3])) t

电话号码1      电话号码2    电话号码3
------------  ----------  -----------
13478908796      12345678     11111

(1 row(s) affected)