字符串合并与拆分写法小结(二)
ct Col1, charindex(',',','+col2), charindex(',',Col2+',') + 1 from SplitStr
union all
select s.Col1, t.p2, charindex(',', s.Col2+',', t.p2) + 1
from SplitStr s join t on s.Col1 = t.Col1 where charindex(',', s.Col2+',', t.p2) > 0
)
--select * from t
select s.Col1, Col2 = substring(s.Col2+',', t.p1, t.p2-t.p1-1)
from SplitStr s join t on s.Col1 = t.Col1
order by s.Col1
option (maxrecursion 0)
3. 使用XML
SELECT A.Col1, B.Code
FROM(SELECT Col1, Code = CONVERT(XML,'
' + REPLACE(Col2, ',', ' ') + ' ') FROM SplitStr) A
OUTER APPLY(SELECT Code = N.v.value('.', 'varchar(100)') FROM A.Code.nodes('/root/v') N(v)) B