通过CTE实现Split CSV

declare @text nvarchar(500)
,
@delimiter nchar(1)
set @text = '1,2,3'
set @delimiter = ','
set @text = @text + @delimiter
;
WITH CSV([index], [comma_index])
as(
select [index] = 1, [comma_index] = CHARINDEX(@delimiter, @text)
union all
select [index] = [comma_index] + 1, [comma_index] = CHARINDEX(@delimiter, @text, [comma_index] + 1)
from csv
where CHARINDEX(@delimiter, @text, [comma_index] + 1) <> 0
)
select SUBSTRING(@text, [index], [comma_index] - [index])
from CSV
where comma_index <> 0

参考前文。 ‘1,2,3’ 将被分解为

1

2

3

posted @ 2011-03-02 17:24 dragonpig 阅读(...) 评论(...) 编辑 收藏