在T-SQL中拆分值

SELECT value
FROM STRING_SPLIT('apple,banana,orange', ',');

CREATE FUNCTION dbo.SplitValues
(
    @inputString NVARCHAR(MAX),
    @delimiter NVARCHAR(10)
)
RETURNS @outputTable TABLE (value NVARCHAR(MAX))
AS
BEGIN
    DECLARE @startPos INT, @endPos INT

    SET @startPos = 1
    SET @endPos = CHARINDEX(@delimiter, @inputString)

    WHILE @endPos > 0
    BEGIN
        INSERT INTO @outputTable (value)
        SELECT SUBSTRING(@inputString, @startPos, @endPos - @startPos)

        SET @startPos = @endPos + LEN(@delimiter)
        SET @endPos = CHARINDEX(@delimiter, @inputString, @startPos)
    END

    INSERT INTO @outputTable (value)
    SELECT SUBSTRING(@inputString, @startPos, LEN(@inputString) - @startPos + 1)

    RETURN
END

SELECT value
FROM dbo.SplitValues('apple,banana,orange', ',');