chakrit

CREATE FUNCTION dbo.__StringSplit(@src nvarchar(4000), @delim NCHAR(1), @refId INT)
    RETURNS @results TABLE (
        ID INT IDENTITY(1, 1),
        Item nvarchar(4000),
        RefID INT
    )
AS
BEGIN

    DECLARE @idx INT
    DECLARE @slice nvarchar(4000)

    SELECT @idx = 1

    -- splitting loop
    WHILE @idx != 0
    BEGIN

        -- Obtain a slice
        SELECT @idx = CHARINDEX(@delim, @src)
        IF @idx != 0
            SELECT @slice = LEFT(@src, @idx - 1)
        ELSE
            SELECT @slice = @src

        -- save the slice result
        INSERT INTO @results (Item, RefID) VALUES (@slice, @refId)

        -- advance by 1 item further into the src string
        SELECT @src = RIGHT(@src, LEN(@src) - @idx)

        IF LEN(@src) = 0
            BREAK
    END

RETURN
END