Search

Monday, November 1, 2010

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');

No comments:

Post a Comment