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))
AS
BEGIN
    -- 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
    BEGIN
        -- Look for the delimiter in the string
        SET @StringValue = 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)
                            );
        -- Store values until the pair is ready for insert
        IF @String IS NULL
            BEGIN
                SET @String      = @StringValue;
                SET @StringValue = NULL;
            END;
        -- Insert the string details into table output
        IF @String IS NOT NULL AND @StringValue IS NOT NULL
            BEGIN
                -- Insert the record into the output table
                INSERT INTO @OutputTable ([String], [String2])
                VALUES                   (@String,  @StringValue);
                SET @String      = NULL;
                SET @StringValue = NULL;
            END;
    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