Most modern web languages these days offer some function to parse a "list" of text items, which is usually a text string with multiple values separated by a delimiter, often a comma. SQL Server unfortunately does not have such an affordance, which becomes troublesome when you're passing a dynamic number of parameters into a SQL statement.
Here comes a user-defined function, which takes a string and a delimiter, and returns a table with the text items broken out into separate rows. To use it:
select t.theString
from dbo.fn_ParseStringList('1,2,3', ',') t
order by t.theString
And here's the code:
create function [dbo].[fn_ParseStringList] (
@input varchar(8000),
@delim char(1)
)
returns @output table (
theString varchar(200)
)
as
begin
-- append the delimiter to the end of the string
set @input = @input + @delim
-- what is the position where the delimiter is located
declare @pos int
set @pos = charIndex(@delim, @input, 1)
while (@pos > 0) begin
if (@pos > 1)
insert into @output (theString)
select left(@input, @pos-1)
set @input = right(@input, len(@input)-@pos)
set @pos = charIndex(@delim, @input, 1)
end
return
end
GO