Wednesday, April 7, 2010

Split string-value with delimeter in Sql Server via XML

Declare @xml as xml
declare @str as varchar(100)
declare @delimiter as varchar(10)
SET @str=',A,B,C,D,E,,,'
SET @delimiter =','
SET @xml = cast((''+replace(@str,@delimiter ,'')+'') as xml)
select @xml
SELECT N.value('.', 'varchar(max)') as value 
FROM @xml.nodes('x') as T(N) where N.value('.', 'varchar(max)')<>''

No comments:

Post a Comment