T-SQL Table-valued Function使用分隔符将字符串转换为表

CREATE FUNCTION [dbo].[F_CM_Split] (@text VARCHAR(MAX), @delimiter VARCHAR(1)=‘ ‘)
RETURNS @Strings TABLE
(
    ITEM_VALUE    VARCHAR(MAX)
)
AS
BEGIN
    DECLARE  @index INT
    SET @index = -1
    WHILE (LEN(@text) > 0)
        BEGIN
            SET @index = CHARINDEX(@delimiter,@text)
            IF (@index = 0) AND (LEN(@text) > 0)
            BEGIN    
                INSERT INTO @Strings VALUES (@text)
            BREAK
        END
        IF (@index > 1)
            BEGIN
            INSERT INTO @Strings VALUES (LEFT(@text,@index - 1))
            SET @text = RIGHT(@text,(LEN(@text) - @index))
            END
        ELSE
            SET @text = RIGHT(@text,(LEN(@text) - @index))
        END
    RETURN
  END
GO