摘要:SQL SERVER UDF-將CSV字串轉成SELECT結果
從這：http://blog.darkthread.net/blogs/darkthreadtw/archive/2009/03/12/split-to-array-sql-udf.aspx
連到這：http://www.sqlteam.com/forums/topic.asp?TOPIC_ID=97682
改了改才看懂…
DECLARE @csv VARCHAR(255)
SET @csv = 'gvfgmgm,f,AAA'
;with s(start) as
(
SELECT distinct charindex(',',','+@csv+',',p)
FROM
(select number p from master..spt_values where type='p' and number<=len(@csv)+2) x
),
chunks as
(
select
substring(@csv,start,(select min(start) from s as s2 where s2.start>s.start)-start-1) RESULT
,@csv CSV,start,(select min(start) from s as s2 where s2.start>s.start)-start-1 PARA2
from s
where start<len(@csv)+2
)
select * from chunks
SET @csv = 'gvfgmgm,f,AAA'
;with s(start) as
(
SELECT distinct charindex(',',','+@csv+',',p)
FROM
(select number p from master..spt_values where type='p' and number<=len(@csv)+2) x
),
chunks as
(
select
substring(@csv,start,(select min(start) from s as s2 where s2.start>s.start)-start-1) RESULT
,@csv CSV,start,(select min(start) from s as s2 where s2.start>s.start)-start-1 PARA2
from s
where start<len(@csv)+2
)
select * from chunks
這段沒看過「select number p from master..spt_values where type='p'」，查了查 master..spt_values似乎是內建的東西；這裡的用途只是提供一串從 1開始的數列。
