MS SQL Tips & Tricks

How to convert comma-delimited string to the table of items

If you need to convert comma-delimited string to a table you can use some SQL2005 XML related advantages. Let's say that we have '1,2,3,4,5,11,22,33,44' that we want to become a table with one value in each row. We just make it look like XML and cast it into XML type for reading. As it is done bellow. 

Declare @cValues as varchar(max); 
set @cValues='1,2,3,4,5,11,22,33,44';

declare @cXML XML;
set @cXML=cast('<a>'+
		REPLACE(@cValues, ',' ,'</a><a>')+'</a>' 
		as XML);

SELECT nref.value('.','nvarchar(50)') as Item
from @cXML.nodes('/a') AS R(nref)

In the result you get

Item
1
2
3
4
5
11
22
33
44

It is probably better to have it as a function... 

CREATE FUNCTION [dbo].[StringToTable](@cStr varchar(max))
RETURNS @retTab table (Item varchar(50))
/* WITH ENCRYPTION */
AS BEGIN
	declare @cXML XML;
	set @cXML=cast('<a>'+REPLACE(
				@cStr, 
				',' ,
				'</a><a>')+'</a>' as XML);
	insert into @retTab(Item)
	SELECT nref.value('.','nvarchar(50)') as val
	from @cXML.nodes('/a') AS R(nref)
  RETURN
END
GO

... and just execute it as 

select * from [dbo].[StringToTable]('1,2,3,4,5,11,22,33,44');

Download SQL script

See also


Learn more tricks

Add Comment

Comments

23rd April 2012

Thank you for your post. It worked for me. You saved my day.

Amit Chhatbar