with cte as (select ssm_id,CHARINDEX(',',a.ext_order_id+',') as station, convert(varchar(10),substring(ext_order_id,1,CHARINDEX(',',a.ext_order_id+',')-1)) as ext_order_id from test as a union all select a.ssm_id,CHARINDEX(',',a.ext_order_id+',',b.station+1) ,convert(varchar(10),substring(a.ext_order_id+',',b.station+1,CHARINDEX(',',a.ext_order_id+',',b.station+1)- b.station-1)) from test as a join cte as b on a.ssm_id=b.ssm_id where CHARINDEX(',',a.ext_order_id+',',b.station+1)>0) select * from cte--结果 ssm_id station ext_order_id ----------- ----------- ------------ 65 6 58507 66 6 58510 107 6 64986 68 6 58143 68 12 58123 68 18 58120 68 24 58119 68 30 58118 107 12 64985 107 18 64982 66 12 58511 65 12 58508(12 行受影响)
http://blog.csdn.net/dotnetstudio/article/details/39341499
(select ssm_id,CHARINDEX(',',a.ext_order_id+',') as station,
convert(varchar(10),substring(ext_order_id,1,CHARINDEX(',',a.ext_order_id+',')-1))
as ext_order_id
from test as a
union all
select a.ssm_id,CHARINDEX(',',a.ext_order_id+',',b.station+1)
,convert(varchar(10),substring(a.ext_order_id+',',b.station+1,CHARINDEX(',',a.ext_order_id+',',b.station+1)-
b.station-1))
from test as a join cte as b on a.ssm_id=b.ssm_id
where CHARINDEX(',',a.ext_order_id+',',b.station+1)>0)
select * from cte--结果
ssm_id station ext_order_id
----------- ----------- ------------
65 6 58507
66 6 58510
107 6 64986
68 6 58143
68 12 58123
68 18 58120
68 24 58119
68 30 58118
107 12 64985
107 18 64982
66 12 58511
65 12 58508(12 行受影响)
locate.这些都是 最基本的函数。肯定有类似的的函数的。