在SQL Server中根据占位符拆分字符串的函数

CREATE FUNCTION dbo.SplitStringByDelimiter
(
    @inputString NVARCHAR(MAX),
    @delimiter NVARCHAR(10)
)
RETURNS @outputTable TABLE (splitValue NVARCHAR(MAX))
AS
BEGIN
    DECLARE @startIndex INT, @endIndex INT

    SET @startIndex = 1
    SET @endIndex = CHARINDEX(@delimiter, @inputString)

    WHILE @endIndex > 0
    BEGIN
        INSERT INTO @outputTable (splitValue)
        SELECT SUBSTRING(@inputString, @startIndex, @endIndex - @startIndex)

        SET @startIndex = @endIndex + LEN(@delimiter)
        SET @endIndex = CHARINDEX(@delimiter, @inputString, @startIndex)
    END

    INSERT INTO @outputTable (splitValue)
    SELECT SUBSTRING(@inputString, @startIndex, LEN(@inputString) - @startIndex + 1)

    RETURN
END

DECLARE @inputString NVARCHAR(MAX) = 'apple,banana,orange'
DECLARE @delimiter NVARCHAR(10) = ','

SELECT splitValue
FROM dbo.SplitStringByDelimiter(@inputString, @delimiter)

splitValue
----------
apple
banana
orange