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))
AS
BEGIN

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

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

    RETURN;

END;
Advertisements

Leave a Reply

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

WordPress.com Logo

You are commenting using your WordPress.com 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