Convert Comma Delimited String To A Table

Welcome back to SQL Something everyone!

Just seeing if I could throw one more post in before the end of the month (trying so hard to stick to my "at least 2 posts per month" rule).

Today we'll look at how to convert a comma delimited string to a table, where each delimited value will be placed in a new row.

Alright let's look at the code below:

----------------------------------------------------------------------------------------------------------------------

DECLARE @StringInput  VARCHAR(100) = 'A01,A02,A03,A04';
DECLARE @StringValue  VARCHAR(100);

CREATE TABLE #DelimitedValues
(
    [Values] [varchar](4) NOT NULL,
)


WHILE LEN(@StringInput) > 0
BEGIN
    SET @StringValue = LEFT(@StringInput,ISNULL(NULLIF(CHARINDEX(',', @StringInput) - 1, -1), LEN(@StringInput)))
    SET @StringInput = SUBSTRING(@StringInput,ISNULL(NULLIF(CHARINDEX(',', @StringInput), 0), LEN(@StringInput)) + 1,LEN(@StringInput))
                                
    INSERT INTO #DelimitedValues
    SELECT @StringValue;
                        
END
                                
SELECT *
FROM #DelimitedValues;

DROP TABLE #DelimitedValues;

----------------------------------------------------------------------------------------------------------------------

You might want to copy the above into a query window for better viewing.

The above takes the string 'A01,A02,A03,A04' and puts each value (A01... etc) in a new row in the temp table #DelimitedValues.

The while loop breaks down the input string until it cannot be broken down further.
The first SET command does the following:

• NULLIF: Checks to see if the there exists at least one comma. It takes the index of the character right before first comma it runs into. Initially the values are: NULLIF(4-1, -1) = 3.

• ISNULL: Takes the value of the character index if one exists, else this value becomes whatever the length of the string at that point in time is. This will mark the end of the value we want to store in the table. Initially the values are: ISNULL(3, 15) = 3.

• LEFT: Takes a string of all characters from the left until the end of the value we wish to store. Initially the values are: LEFT(A01,A02,A03,A04, 3) = A01.

The second SET statement does much of the same except it pulls out the remainder of the string, minus the value we got in the first SET. Initially this would be: 'A02,A03,A04'.

Finally it inserts the value into the table and sets the old input string value to the string of remaining values.


DISCLAIMER: As stated, I’m not an expert so please, PLEASE feel free to politely correct or comment as you see fit. Your feedback is always welcomed. :-)