Split using CTE

Create function [dbo].[fn_Split](
@String nvarchar (4000),
@Delimeter nvarchar (10)
)
returns @RetTable table (pos int IDENTITY(1,1), String nvarchar(4000))
Begin
set @String = @String + @Delimeter;

with MySplit(x,y,z,Result)
as
(
select x = @String, y= @Delimeter , z = 0, Result = @String
union all
select ltrim(substring(x,charindex(y,x)+len(y),len(x))),y,z+charindex(y,x),substring(x,0,charindex(y,x)) from MySplit where len(x) >= len(y) and len(Result) >0
)

insert into @RetTable select Result from MySplit where z>0

return
End


--more than 100 entries are not supported here, so if the result is more than 100 rows then it will geve error