there is always something to develop
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
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');
23rd April 2012
Thank you for your post. It worked for me. You saved my day.
"Don't go around saying the world owes you a living; the world owes you nothing; it was here first "
You are 9293 reader
since 1st April 2009