SQL Server根据逗号拆分字符串

CREATE FUNCTION dbo.SplitString
(
    @str nvarchar(max),
    @separator char(1)
)
RETURNS @result TABLE (val nvarchar(max))
AS
BEGIN
    DECLARE @startPos int, @endPos int

    SET @startPos = 1
    SET @endPos = CHARINDEX(@separator, @str)

    WHILE @endPos > 0
    BEGIN
        INSERT INTO @result(val)
        VALUES(SUBSTRING(@str, @startPos, @endPos - @startPos))

        SET @startPos = @endPos + 1
        SET @endPos = CHARINDEX(@separator, @str, @startPos)
    END

    INSERT INTO @result(val)
    VALUES(SUBSTRING(@str, @startPos, LEN(@str) - @startPos + 1))

    RETURN
END

SELECT val 
FROM dbo.SplitString('1,2,3,4,5', ',')

val
-----
1
2
3
4
5

DECLARE @xml xml

SET @xml = CAST('<root><v>' + REPLACE('1,2,3,4,5', ',', '</v><v>') + '</v></root>' as xml)

SELECT Tbl.Col.value('.', 'nvarchar(max)') as val
FROM @xml.nodes('/root/v') Tbl(Col)

SELECT value as val
FROM STRING_SPLIT('1,2,3,4,5', ',')