Th. Fuchs (10/11/2013)

---

-- f.e.: to split the comma delimited string into rows without using LOOP, CTE, XML.

declare @p nvarchar(max) = '', @start datetime

select @p += name + ',' from master.sys.sysobjects

select items = @@rowcount + 1

select @p += 'End of Teststring'

select lentotal = len(@p)

print 'Teststring: ''' + @p + ''''

select @start = getdate()

;with zahlen(n) as (select 1 union all select n + 1 from zahlen where n < datalength(@p))

select

--n = case when n = 1 then 0 else n + 1 end, -- start delimiter found

--m = isnull(nullif(charindex(',', @p, n + 1), 0), len(@p)), -- calulated pos of next delimiter

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

-- - n - case when n = 1 then 0 else 1 end, -- calulated len to next delimiter

[substring] = substring(@p,

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

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

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

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

else -1 end -- len of token

)

from zahlen where n = 1 or substring(@p, n, 1) = ','

option (maxrecursion 0);

print 'time used ' + cast(cast(datediff(ms, @start, getdate()) / 1000.0 as decimal(16,2)) as varchar(8))

+ ' seconds for ' + cast(@@rowcount as varchar(8)) + ' rows'