TSQL Function that Tables a Delimited String

Use today’s slab to turn a delimited string into table variable.

CREATE FUNCTION [dbo].[udf_Split](@text VARCHAR(8000), @delimiter CHAR(1) = ' ')
-- =============================================
-- Author:      Corey Furman
-- Create date: 1 Nov 2008
-- Description: Takes in a delimited string and returns a table
-- =============================================
RETURNS @TableVar TABLE
(
    Ordinal INT IDENTITY PRIMARY KEY,
    Value   VARCHAR(8000)
)
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 @TableVar VALUES (LTRIM(RTRIM(@text)))
            BREAK
        END

        IF (@index > 1)
        BEGIN
            INSERT INTO @TableVar VALUES (LTRIM(RTRIM(LEFT(@text, @index - 1))))
            SET @text = RIGHT(@text, (LEN(@text) - @index))
        END
        ELSE
            SET @text = RIGHT(@text, (LEN(@text) - @index))
    END

    RETURN
END

You can call this function like this:

-- to specify what to use as a delimiter
SELECT * FROM dbo.udf_Split('dog, cat, hamster', ',')

-- to take advantage of the default value of the delimiter parameter, specify the DEFAULT keyword
SELECT * FROM dbo.udf_Split('pig horse chicken', DEFAULT)