The code I use to split out delimited list is:

create function [dbo].[Split](@value nvarchar(max), @delimiter varchar(20))

returns @results table (id int identity(1, 1) primary key, value nvarchar(max))

as

begin

declare @p1 int,

@p2 int

-- ensure value ends with the delimiter character

if right(@value, len(@delimiter)) != @delimiter

set @value = @value + @delimiter

set @p1 = 1

set @p2 = charindex(@delimiter, @value)

while @p2 > @p1

begin

insert into @results (value)

select substring(@value, @p1, @p2 - @p1)

set @p1 = @p2 + len(@delimiter)

set @p2 = charindex(@delimiter, @value, @p1)

end

return

end

go

My initial tests show it to perform faster than your CTE based solution, but I would welcome other peoples results.