String Splitting Function by Delimiter

Posted: February 2, 2009 in SQL Server 2005

To create a function that will take a delimited string and return the values as a table, open a new query window and execute the following line of code:

-- Function to return string of values as a table with single values
CREATE FUNCTION [dbo].[f_MySplitRoutine]
               (@StringInput VARCHAR(MAX)
               ,@Delimiter VARCHAR(1)
RETURNS @OutputTable TABLE ([String] VARCHAR(100))

    -- Declare variables
    DECLARE @String VARCHAR(10);

    -- Loop through the string right up to the end
    WHILE LEN(@StringInput) > 0
        -- Look for the delimiter in the string
        SET @String = LEFT(@StringInput
                            (NULLIF(CHARINDEX(@Delimiter, @StringInput) – 1, -1)
        -- Extract the portion of the string that has been checked
        SET @StringInput = SUBSTRING
                              (NULLIF(CHARINDEX(@Delimiter, @StringInput), 0)
                              ) + 1, LEN(@StringInput)
        -- Insert the record into the output table
        INSERT INTO @OutputTable ([String]) VALUES (@String);



Leave a Reply

Fill in your details below or click an icon to log in: Logo

You are commenting using your account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s