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
Item |
1 |
2 |
3 |
4 |
5 |
11 |
22 |
33 |
44 |
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');
See also
Comments
"Experience is simply the name we give our mistakes. "
You are 13998 reader
since 1st April 2009
Yes | 13.3% |
10 |
No | 86.7% |
65 |