22,210
社区成员
发帖
与我相关
我的任务
分享
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)
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 行受影响)
*/