/* A query to turn a list into a result set containing the list items, one per row. Steve Kass, Drew University */ --A table of integers is needed create table Seq ( Nbr int not null ) insert into Seq select top 4001 0 from Northwind..[Order Details] cross join (select 1 as n union all select 2) X declare @i int set @i = -1 update Seq set @i = Nbr = @i + 1 alter table Seq add constraint pk_Seq primary key (Nbr) --table Seq created go create procedure ParseList ( @List varchar(8000) ) as --This makes things more readable. The list is easier --to process if it begins and ends with a single comma --As it turns out also, list items cannot --have leading or trailing spaces (here any leading spaces --in the first item or trailing spaces in the last are --eliminated) set @List = replace(rtrim(','+ltrim(@List))+',', ',,', ',') --The items are extracted by selecting those substrings of --the list that begin immediately after a comma and end --immediately before the next comma, then trimming spaces on --both sides. select distinct ltrim(rtrim( substring(@List, commaPos+1, charindex(',', @List, commaPos+1) - (commaPos+1)))) as Item from ( --This query returns a table containing one column, commaPos, --of integers, the positions of each comma in @List, except the last select Nbr as commaPos from Seq where substring(@List,Nbr,1) = ',' and Nbr < len(@List) ) N go --examples declare @x varchar(4000), @time datetime set @x = replicate('foo,bar,', 4000/8 - 1) + 'ab' exec Parselist @x set @x = 'the fox jumped on the rabbit' declare @s varchar(100) set @s = replace(@x,' ',',') exec Parselist @s --Note, if a list contains a non-comma delimiter, and contains no --commas within items, this replacement allows the function to --handle it. If a comma appears in an item, but some other non- --delimiter is absent from the list, a three-step replacement can --be made: -- replace all commas with new character not in list -- replace all delimiters with comma -- Use (select replace(Item,,) from ListTable(@List)) LT -- where the list table is used. go --Since this is a repro script, delete everything! --Keep them around if they are helpful, though. DROP procedure ParseList DROP TABLE Seq