Display Comma Separated Value Row Wise in SQL Server

In this post, we will learn how to display comma separated value row wise in SQL Server

Example-1: Comma separated values of a variable to rows
In the first Example we will take a variable which holds some values with comma separator and we will display each value in a row.

Step 1: Replace comma with tag from comma separated value list to make it as XML
Step 2: Read each node from XML and display value in rows

?

1 2 3 4 5

DECLARE @xml XML, @s NVARCHAR(MAX) SELECT  @s = N'a,b,c,d,e' SET     @xml = N'<t>' + REPLACE(@s,',','</t><t>') + '</t>' SELECT  t.value('.','varchar(5)') as [delimited items] FROM    @xml.nodes('/t') as a(t)

OUTPUT

Example-2: Comma separated values of a column to rows
In this example, we have two columns Col1 & Col2 in a table and Col2 stores comma separated values and our requirement is to display each value of Col2 in row form against Col1

Step 1: Replace comma with tag from comma separated value list to make it as XML
Step 2: Reading each node from the XML
Step 3: Cross Apply to generated each node value as row.

Suggested article to understand Cross Apply: APPLY Operator in SQL Server

?

1 2 3 4 5 6 7 8 9 10 11

DECLARE @TAB TABLE (col1 varchar(10), col2 varchar(10)) INSERT INTO @TAB SELECT 'A' as col1, '1,2,3' as col2 UNION SELECT 'B' as col1, '4,5,6' as col2   SELECT col1, Tags.val.value('.', 'VARCHAR(MAX)') AS col2 FROM(     SELECT  col1, CAST('<t>' + REPLACE(col2, ',', '</t><t>') + '</t>' AS XML) AS TAG     FROM    @TAB ) T CROSS APPLY TAG.nodes('/t') as Tags(val)

OUTPUT

Now let's transform the above code into a table valued function to make it reusable and flexible.

?

1 2 3 4 5 6 7 8 9 10 11 12 13

CREATE FUNCTION Split(     @delimited NVARCHAR(MAX),     @delimiter NVARCHAR(100) ) RETURNS @t TABLE (id INT IDENTITY(1,1), val NVARCHAR(MAX)) AS BEGIN     DECLARE @xml XML     SET @xml = N'<t>' + REPLACE(@delimited,@delimiter,'</t><t>') + '</t>'     INSERT INTO @t(val)     SELECT  r.value('.','varchar(MAX)') as item     FROM  @xml.nodes('/t') as records(r)     RETURN END

Now, In the above table valued function we have to just pass delimited string and delimiter and it will return you a table with two columns id (sequential number) and val (all delimited value).
Delimiter could be any : Comma, Colon, Semi-Colon etc.

Suggested article to understand split function in detail: Split Function in SQL

Now, let's execute the table valued function with different delimiters

?

1 2	SELECT * FROM dbo.Split(N'a,b,c', ',') SELECT * FROM dbo.Split(N'p:q:r', ':')

OUTPUT