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