Not a bad solution but using a recursive CTE and a multi-statement TVF this function is not very scalable.

Here is another solution to this problem:

CREATE function [dbo].[DelimitedSplit] (

@pString varchar(max),

@pDelimiter char(1)

)

returns table

as

return

with

a1 as (select 1 as N union all

select 1 union all

select 1 union all

select 1 union all

select 1 union all

select 1 union all

select 1 union all

select 1 union all

select 1 union all

select 1),

a2 as (select

1 as N

from

a1 as a

cross join a1 as b),

a3 as (select

1 as N

from

a2 as a

cross join a2 as b),

a4 as (select

1 as N

from

a3 as a

cross join a2 as b),

Tally as (select top (len(@pString))

row_number() over (order by N) as N

from

a4),

ItemSplit(

ItemOrder,

Item

) as (

SELECT

N,

SUBSTRING(@pDelimiter + @pString + @pDelimiter,N + 1,CHARINDEX(@pDelimiter,@pDelimiter + @pString + @pDelimiter,N + 1) - N - 1)

FROM

Tally

WHERE

N < LEN(@pDelimiter + @pString + @pDelimiter)

AND SUBSTRING(@pDelimiter + @pString + @pDelimiter,N,1) = @pDelimiter --Notice how we find the delimiter

)

select

row_number() over (order by ItemOrder) as ItemID,

Item

from

ItemSplit