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.
"Laughing at our mistakes can lengthen our own life. Laughing at someone else's can shorten it."
You are 10449 reader
since 1st April 2009