/*
  A table-valued function with one parameter, a delimited list,
  that returns the separate distinct items of the list.
  Steve Kass, Drew University
  Thanks to MVPs Linda Wierzbicki and Umachandar Jayachandran
  for help and helpful discussions on this.
*/ 

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

--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)
create function RegularizedList (@List varchar(8000))
returns varchar(8000) as begin
return replace(rtrim(','+ltrim(@List))+',', ',,', ',')
end
go

--This function returns a table containing one column, commaPos,
--of integers, the positions of each comma in @List, except the last
create function nonTerminalCommaPositions (@List varchar(8000))
returns table
as return
select Nbr as commaPos from Seq
where substring(@List,Nbr,1) = ','
and Nbr < len(@List)
go

--This function returns a table containing the items in the 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.
create function ListTable (@List varchar(8000))
returns @t table (
  Item varchar(8000)
) as begin
set @List = dbo.RegularizedList(@List)
insert into @t
select distinct ltrim(rtrim(
    substring(@List,
       commaPos+1,
        charindex(',', @List, commaPos+1) - (commaPos+1))))
from
  dbo.nonTerminalCommaPositions(@List)
return
end
go

--examples
   declare @x varchar(4000), @time datetime
   set @x = replicate('foo,bar,', 4000/8 - 1) + 'ab'
   select * from ListTable(@x)

   set @x = '10245  10345 98292  '
   declare @s varchar(400)
   set @s = replace(@x,' ',',')
   select * from ListTable(@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,<new>,<comma>) 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 FUNCTION RegularizedList
DROP FUNCTION nonTerminalCommaPositions
DROP TABLE Seq
DROP FUNCTION ListTable