MS SQL Tips & Tricks

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',',');

Download SQL script

Learn more tricks

Add Comment

Comments

17th June 2011

Polskie nazwisko a funkcja nie unicode :p

ax