Convert Comma Separated Values to Column Values in Table

There are situations where you may want to convert comma separated values to a column values in a table. Here are two useful ways for achieving the same.

Method 1 – Using xml


DECLARE @message VARCHAR(500) = 'one,two,three,four,five'

DECLARE @test XML = Cast('<A>' + REPLACE(@message, ',', '</A><A>') + '</A>' AS XML)

SELECT t.value('.', 'NVARCHAR(MAX)') Value FROM @test.nodes('/A') AS x(t)

Here is the result of above query.

CSVToColumnResult1

Method 2 – Using Common Table Expression (CTE)


DECLARE @csv VARCHAR(100) = '1,2,3,4,'

;WITH CTE (charr, commaIndex)

AS

(

SELECT SUBSTRING(@csv, 1, CHARINDEX(',', @csv, 1) - 1), CHARINDEX(',', @csv, 1)

UNION ALL

SELECT SUBSTRING(@csv, commaIndex + 1, CHARINDEX(',', @csv,                          commaIndex + 1) - commaIndex - 1),    CHARINDEX(',', @csv, commaIndex + 1)           FROM CTE    WHERE commaIndex + 1< LEN(@csv)

) SELECT charr FROM CTE;

Here is the result of above query.

CSVToColumnResult2

Leave a Reply

Please log in using one of these methods to post your comment:

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