String Splitting Function by Delimiter – Into Columns

Posted: March 18, 2009 in SQL Server 2005

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

-- Function to return string of values as a table with column values
CREATE FUNCTION [dbo].[f_MySplitRoutine2]
               (@StringInput VARCHAR(MAX)
               ,@Delimiter   VARCHAR(1)
RETURNS @OutputTable TABLE ([String] VARCHAR(100), [String2] VARCHAR(100))
    -- Declare variables
    DECLARE @String      VARCHAR(10);
    DECLARE @StringValue VARCHAR(10);

    -- Initialise variables
    SET @String = NULL;

    -- Loop through the string right up to the end
    WHILE LEN(@StringInput) > 0
        -- Look for the delimiter in the string
        SET @StringValue = 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)
        -- Store values until the pair is ready for insert
        IF @String IS NULL
                SET @String      = @StringValue;
                SET @StringValue = NULL;
        -- Insert the string details into table output
        IF @String IS NOT NULL AND @StringValue IS NOT NULL
                -- Insert the record into the output table
                INSERT INTO @OutputTable ([String], [String2])
                VALUES                   (@String,  @StringValue);
                SET @String      = NULL;
                SET @StringValue = NULL;



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