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.
"The problem is never how to get new, innovative thoughts into your mind, but how to get old ones out. Every mind is a building filled with archaic furniture. Clean out a corner of your mind and creativity will instantly fill it."
You are 9595 reader
since 1st April 2009