Sorry, I did forget to include the splitstring function:
CREATE function [dbo].[SplitString]
(
@STR nvarchar(4000),
@separator char(1)
)
returns table
AS
return (
with tokens(p, a, b) AS (
select
1,
1,
charindex(@separator, @STR)
union all
select
p + 1,
b + 1,
charindex(@separator, @STR, b + 1)
from tokens
where b > 0
)
select
p-1 zeroBasedOccurance,
substring(
@STR,
a,
case when b > 0 then b-a ELSE 4000 end)
AS s
from tokens
)
GO
Edit BTW, this was probably not original, but if it isn't I'm not sure of the source.