MS SQL Server Recursive T-SQL Split Function

Here you can find a t-sql split function which uses recursive CTE (common table expressions) structure in its source code.

This sample t-sql recursive split function will work with Microsoft SQL Server 2005 and MS SQL Server 2008 successfully.

Here is a sample usage of the t-sql SPLIT function in action

declare @s nvarchar(max)
select @s = N',,45 , 1, 56, 3456 456,8,5, ,d,1,1,4, 5 9 ,t,,,4,5 ,,,,, w, 3,,'
select * from dbo.split(@s,1,0)

CREATE FUNCTION SPLIT
(
@s nvarchar(max),
@trimPieces bit,
@returnEmptyStrings bit
)
returns @t table (val nvarchar(max))
as
begin

declare @i int, @j int
select @i = 0, @j = (LEN(@s) - LEN(replace(@s,',','')))

;with cte
as
(
select
i = @i + 1,
s = @s,
n = substring(@s, 0, charindex(',', @s)),
m = SUBSTRING(@s, charindex(',', @s) + 1, LEN(@s) - charindex(',', @s))

union all

select
i = cte.i + 1,
s = cte.m,
n = substring(cte.m, 0, charindex(',', cte.m)),
m = SUBSTRING(cte.m, charindex(',', cte.m) + 1, LEN(cte.m) - charindex(',', cte.m))
from cte
where i <= @j
)
insert into @t (val)
select pieces
from
(
select
case
when @trimPieces = 1 then LTRIM(rtrim(case when i <= @j then n else m end))
else case when i <= @j then n else m end
end
as pieces
from cte
) t
where
(@returnEmptyStrings = 0 and LEN(pieces) > 0) or (@returnEmptyStrings = 1)
option (maxrecursion 0)

return

end

GO


BlinkList   Del.icio.us   Digg   Furl   Simpy   Spurl   DZone   ma.gnolia   Shadows