CREATE FUNCTION [dbo].[f_splitSTR](
@string VARCHAR(MAX),   --待分拆的字符串
@splitChar VARCHAR(10)     --數據分隔符
)RETURNS @result TABLE(col VARCHAR(100))
AS
BEGIN
DECLARE @splitLen INT
SET @splitLen=LEN(@splitChar)-1
WHILE CHARINDEX(@splitChar,@string)>0
BEGIN
    INSERT @result VALUES(LEFT(@string,CHARINDEX(@splitChar,@string)-1))
    SET @string=STUFF(@string,1,CHARINDEX(@splitChar,@string)+@splitLen,'')
END
INSERT @result VALUES(@string)
RETURN
END
GO


SELECT * FROM [dbo].[f_splitSTR]('Id1,Id2,Id3,Id4',',')