Th. Fuchs (10/14/2013)

---

---- drop the recursion into a static table

if object_id('dbo._numbers') is null --- drop table dbo._numbers

begin

print 'create static collection of numbers'

create table dbo._numbers (n integer primary key(n))

declare @i integer = 0

set nocount on

while @i <= 214748 --3647 -- the hidden recursion

begin insert into dbo._numbers(n) values(@i) select @i += 1 end

end

select @start = getdate()

select [substring] = substring(@p,

case when n = 0 then 0 else n + 1 end, -- start of token

isnull(nullif(charindex(',', @p, n + 1), 0), len(@p)) - n +

case when n = 0 then 0 -- start at position 0

when charindex(',', @p, n + 1) = 0 then 0 -- the last token

else -1 end -- len of token - len(delimiter)

)

from dbo._numbers where n = 0 or substring(@p, n, 1) = ','

---- or hide recursion in the stack (attention, max 31 item pssible)

create function dbo.stt(@source varchar(8000)) returns @t table (t varchar(8000)) as

begin

declare @token varchar(8000), @l integer

select @l = @@nestlevel

if @source like '%,%'

begin -- token exists

select @token = left(@source, charindex(',', @source) -1) -- cut first token

select @source = right(@source, len(@source) - len(@token) -1) -- trimm tail

insert into @t(t) values(@token) -- the one token found into resultset

insert into @t(t) select t from dbo.stt(@source) -- the same procedure for the rest THE RECURSION

end -- first token cutted

else

begin -- last token found

insert into @t(t) values(@source) -- the last feather

end -- now ready

return

end