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

Learn more tricks

Add Comment

Comments

23rd April 2012

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

Amit Chhatbar

8th September 2021

viagra and atrial fibrillation

Invorne

12th September 2021

http://buystromectolon.com/ - Stromectol

uribete

16th September 2021

cialis ne marche pas

Priligy