|
|
Recursive Function Sample - MS SQL Server Recursive T-SQL Split Function
Here in this tutorial you can find a recursive function sample T-SQL split function which uses recursive CTE (common table expressions) structure in its source code.
If you are working as a SQL Developer or working as an database administrator (DBA), you might probably require a handy t-sql user-defined function, stored procedure or statement that will split an input string (nvarchar data) into pieces according to a given seperator character.
I'm working on my company as a developer and frequently I had to develop t-sql code blocks that will solve problems on SQL Server. T-SQL Recursive Split User-Defined function is one that I programmed for splitting text.
This sample t-sql recursive split function will work successfully on Microsoft SQL Server 2005 and MS SQL Server 2008 sql engines.
Here is an example usage of the T-SQL SPLIT function in action
declare @s nvarchar(max)
select @s = N',,45 , 1, 56, 346 456,8,5, ,d,1,4, 5 9 ,t,,4,5 ,,, w, 3,,'
select * from dbo.split(@s,1,0)
And you can find the t-sql source code or the sql definition of the T-SQL Recursive Split Function.
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
I hope this recursive function for splitting strings in sql will be useful for you.
|
Related SQL Resources
SQL Server Articles
SQL Blog
SQL Server 2008 Blog
Certification Exams Blog
Reporting Services Blog
Analysis Services Blog
MS SQL Server Forums
|