there is always something to develop
How to split string in SQL
If you need to split 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); Declare @cDelimiter as char(1); set @cValues='1,2,3,4,5,11,22,33,44'; set @cDelimiter=',' declare @cXML XML; set @cXML=cast('<a>'+ REPLACE(@cValues, @cDelimiter ,'</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].[SplitToTable](@cStr varchar(max), @cDelimiter char(1)) RETURNS @retTab table (Item varchar(50)) /* WITH ENCRYPTION */ AS BEGIN declare @cXML XML; set @cXML=cast('<a>'+REPLACE( @cStr, @cDelimiter , '</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].[SplitToTable]('1,2,3,4,5,11,22,33,44',',');
See also
Comments
"If you do little things well, you'll do big ones better."
You are 6158 reader
since 1st April 2009
Yes | 60.0% |
3 |
No | 40.0% |
2 |