學海無邊，書囊無底

如何將逗號分隔的字串透過T-SQL回傳成一組DataSet呢。
例如輸入 Sandy,Marry,Dofi 一組字串時，回傳下列三筆資料 。
          Sandy
          Marry
          Dofi

CREATE FUNCTION dbo.ufn_SplitToTable
( @InputString nvarchar(4000)
)
RETURNS 
@tblReturn TABLE (COL1 nvarchar(60)) 

AS
BEGIN
	DECLARE @CIndex smallint
	WHILE (@InputString<>'')
	BEGIN
		SET @CIndex=CHARINDEX(',',@InputString)
		IF @CIndex=0 SET @CIndex=LEN(@InputString)+1
		
        --透過substring函數取得第一個字串，並輸入資料表變數中
		INSERT INTO @tblReturn (COL1) 
		VALUES (SUBSTRING(@InputString,1,@CIndex-1))
		
		IF @CIndex=LEN(@InputString)+1 BREAK
		SET @InputString=SUBSTRING(@InputString,@CIndex+1,LEN(@InputString)-@CIndex)
	END
	RETURN 
END
GO

最後使用下列語法測試下
Select * From dbo.ufn_SplitToTable('Sandy,Lisa,Dofi')
執行結果如下：
 

以我的設計經驗來說，我通常都會將這個自訂函數拿來模擬多重選單的功能。以下列這個預存程序為例，假設我要查詢客戶編號為 ALFKI 與 ANATR 這兩筆資料。我的T-SQL語法應該是
SELECT * FROM Northwind.dbo.customers
WHERE CUSTOMERID IN (‘ALFKI’ , ’ANATR’)
但如果改成預存程序呢？WHERE子句應該如何處理呢？其實只要將上述的自訂函數拿來稍微應用一下即可。參考下列指令碼：

Create proc sp_QueryCustomers @STR nvarchar(max)
AS
SELECT CustomerID,CompanyName,ContactName,Phone 
FROM dbo.Customers
WHERE  CustomerID IN (select * from dbo.ufn_SplitToTable(@STR))
GO
--使用下列語法執行
DECLARE @STR NVARCHAR(MAX)
SET @STR='ALFKI,ANATR'
EXEC sp_QueryCustomers @STR

應該不難發現，ufn_SplitToTable自訂函數神奇地將字串切割成資料集了。因此在CustomerID in(參數)這裡可以將符合的字串挑回來。執行結果如下圖所示。

最後，在示範一個稍微複雜的字串處理。這次需要處理的字串是"22,科技類;26,旅遊類;27,進修類"，然後我們希望回傳的資料集如下：

欄位1  欄位2
-------------------
22    科技類
26    旅遊類
27    進修類

CREATE   FUNCTION [dbo].[fnSplitList](@List Nvarchar(MAX))
--取出的字串長相需是 ======= 22,科技類;26,旅遊類;27,進修類
	RETURNS @ReturnTable TABLE(Column_code nvarchar(30),Column_Desc nvarchar(60))
AS 
BEGIN
	DECLARE @Index int,@IndexCol int
	DECLARE @NewText nvarchar(max),@ColumnCode nvarchar(max),@ColumnDesc nvarchar(max) 
	IF @List = null
		RETURN
	SET @Index = CHARINDEX(';', @List)
	WHILE NOT(@Index = 0)
	BEGIN
		--取出 22,科技類	
		SET @NewText = RTRIM(LTRIM(LEFT(@List, @Index - 1)))

		--切割成 <22> 和 <科技類> 兩部分
		SET @IndexCol=CHARINDEX(',',@NewText)
		SET @ColumnCode = RTRIM(LTRIM(LEFT(@NewText, @IndexCol - 1)))
		SET @ColumnDesc = RIGHT(@NewText,LEN(@NewText)-@IndexCol)

		--插入紀錄
		INSERT INTO @ReturnTable(Column_Code,Column_Desc) VALUES(@ColumnCode,@ColumnDesc)

		--取出前一段字串後剩餘的字串
		SET @List = RIGHT(@List, LEN(@List) - @Index)
		SET @Index = CHARINDEX(';', @List)
    	END
		 
		--切割成 <26> 和 <旅遊類> 兩部分
		SET @IndexCol=CHARINDEX(',',@List)
		SET @ColumnCode = RTRIM(LTRIM(LEFT(@List, @IndexCol - 1)))
		SET @ColumnDesc = RIGHT(@List,LEN(@List)-@IndexCol)

		--插入紀錄
		INSERT INTO @ReturnTable(Column_Code,Column_Desc) VALUES(@ColumnCode,@ColumnDesc)
         
     RETURN
END

最後，使用下列語法測試回傳結果

DECLARE @STR NVARCHAR(MAX) SET @STR=N'22,科技類;26,旅遊類;27,進修類'
SELECT * FROM [dbo].[fnSplitList](@STR)

成功地將一組字串組成虛擬資料表，如下圖所示

以上程式碼適用於SQL Server 2000以上版本。SQL 2005 則可參考下列程式碼達到 split 的效果。

create function Split ( 
@StringToSplit varchar(2048),
@Separator varchar(128))
returns table as return
with indices as
( 
select 0 S, 1 E
union all
select E, charindex(@Separator, @StringToSplit, E) + len(@Separator) 
from indices
where E > S 
)
select substring(@StringToSplit,S, 
case when E > len(@Separator) then e-s-len(@Separator) else len(@StringToSplit) - s + 1 end) String
,S StartIndex        
from indices where S >0
GO

--simple split comma delimited string example
select * from Split('aaa,b,cccc,dd,e,ffff,g' , ',')

--when the separator is not found, no problems either
select * from Split('abcdefgh' , ',')

--empty values are returned too, the list would not be complete without them (for example when handling input file lines)
select * from Split(',,a,,b,c,,,d' , ',')

--skipping the empty values would simply be using a criterium
select * from Split(',,a,,b,c,,,d' , ',') where len(String) > 0

--of course, splitting with other (and longer) strings than a comma is supported too
select * from Split('aaa->bb->cccc->d' , '->') where len(String) > 0

--just a final example with some query options combined
select rtrim(ltrim(String)) from Split('from abc from abb from cdd from addd' , 'from') 
where ltrim(String) like 'a%'

參考：Split String Function T-SQL