將字串依分隔符號轉成Table，是依分隔符號在那邊然後去擷取中間的字，然後新增到Table之中。
而本文章提供另一個透過XML + CROSS APPLY 的方式，也可以達到一樣的目的。
之前將字串依分隔符號轉成Table，是依分隔符號在那邊然後去擷取中間的字，然後新增到Table之中。
可以參考「fn_Split」
另外，我們也可以參考「SQL SERVER – Split Comma Separated List Without Using a Function」這篇的方式，
轉成XML後，再取出每個 node 裡的值，如下，
SELECT CAST('<XMLRoot><RowData>' + REPLACE('foo,bar,widget',',','</RowData><RowData>') + '</RowData></XMLRoot>' AS XML) AS x;
會轉成以下的XML，
<XMLRoot>
<RowData>foo</RowData>
<RowData>bar</RowData>
<RowData>widget</RowData>
</XMLRoot>
所以再透過 CROSS APPLY 來取出每個 RowData 裡的值，如下，
SELECT LTRIM(RTRIM(n.value('.[1]','varchar(8000)'))) AS value
FROM
(
SELECT CAST('<XMLRoot><RowData>' + REPLACE('foo,bar,widget',',','</RowData><RowData>') + '</RowData></XMLRoot>' AS XML) AS x
) t
CROSS APPLY x.nodes('/XMLRoot/RowData')m(n);
所以可以封裝一下 fn_Split 的 XML版本，如下，
SET QUOTED_IDENTIFIER ON
GO
-- drop function dbo.fn_SplitXML
CREATE FUNCTION dbo.fn_SplitXML(@text nvarchar(max), @delimiter varchar(20) = ',')
RETURNS @Strings TABLE
(
position int IDENTITY PRIMARY KEY,
value nvarchar(max)
)
AS
BEGIN
INSERT INTO @Strings
SELECT LTRIM(RTRIM(m.n.value('.[1]', 'varchar(8000)'))) AS value
FROM ( SELECT CAST('<XMLRoot><RowData>' + REPLACE(@text, @delimiter,
'</RowData><RowData>')
+ '</RowData></XMLRoot>' AS XML) AS x
) t
CROSS APPLY x.nodes('/XMLRoot/RowData') m ( n )
RETURN
END
GO
而使用上就跟 fn_Split 一樣，如下，
SELECT * FROM fn_Split('foo,bar,widget', ',');
SELECT * FROM fn_SplitXML('foo,bar,widget', ',');
以上提供另一個思考的方式。
程式雖然簡潔了，但因為轉成XML再取出 node ，會比較花時間。
而且 value 無法轉成 nvarchar 字串哦! 所以使用上要注意一下哦!
參考資料
閱讀Using SQL Server Table Variables to Eliminate the Need for Cursors感想
SQL SERVER – Split Comma Separated List Without Using a Function
Hi,
亂馬客Blog已移到了 「亂馬客 : Re:從零開始的軟體開發生活」
請大家繼續支持 ^_^
