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 @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)