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

If you want to convert comma-delimited string to a table you can use some SQL 2005 XML related advantages. Let's say that we have '1,2,3,4,5,10,20,30,40' 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 shown bellow.

Declare @cValues as varchar(max);

set @cValues='1,2,3,4,5,10,20,30,40';

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
10
20
30
40

It is 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 execute it as

select * from [dbo].[StringToTable]('1,2,3,4,5,10,20,30,40');