SQL server split received string using XML

Several time, we have a need to split the string based on a delimiter, for instance to handle multiple input filter parameters etc.

 

Most of the time we end up using a user defined function that accepts a string and a delimiter and returns a table of split values. Function may be in-efficient at times.

 

Here is an XML way to split the input string and get a result within a table

 

declare @xml as xml,@str as varchar(100),@delimiter as varchar(10)set @str='A,B,C,D,E'set @delimiter =','set @xml = cast(('<X>' + replace(@str, @delimiter, '</X><X>') + '</X>') as xml)select N.value('.', 'varchar(10)') as value from @xml.nodes('X') as T(N)