[[Function] Split]

Split.sql

CREATE FUNCTION [_My_Split]
(  
	@InputStr	nvarchar(max),	--原始字串
	@SplitStr	varchar(50)		--分割符號
)
RETURNS @tblReturn table (Word nvarchar(60)) 
AS
BEGIN
	DECLARE @CIndex smallint
	WHILE (@InputStr<>'')
	BEGIN
		SET @CIndex=CHARINDEX(@SplitStr,@InputStr)
		IF @CIndex=0 SET @CIndex=LEN(@InputStr)+1
        --透過substring函數取得第一個字串，並輸入資料表變數中
        IF @CIndex <> 1	--排除空字串
			BEGIN
				INSERT INTO @tblReturn (Word) 
				VALUES (SUBSTRING(@InputStr,1,@CIndex-1))
			END
		IF @CIndex=LEN(@InputStr)+1 BREAK
		SET @InputStr=SUBSTRING(@InputStr,@CIndex+1,LEN(@InputStr)-@CIndex)
	END
	RETURN 
END
GO

--Example
SELECT * FROM _My_Split('aaa,,bbb,ccc',',')