SQL Server administration and T-SQL development, Web Programming with ASP.NET, HTML5 and Javascript, Windows Phone 8 app development, SAP Smartforms and ABAP Programming, Windows 7, Visual Studio and MS Office software Kodyaz SQL Server and T-SQL Development Tutorials
Development resources, articles, tutorials, samples, codes and tools for .Net, SQL Server, Vista, etc.




download SQL Server 2016
download SQL Server 2014



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.

MSSQL Split Function Example

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 (id int identity(1,1), 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

mssql-split-comma-separated-list

I hope this MSSQL split recursive function for splitting strings in sql will be useful for you.

Here is an other version of the above T-SQL split string function which returns a number showing the order of the splitted string.

CREATE FUNCTION SPLIT
(
 @s nvarchar(max),
 @splitChar nchar(1)
)
returns @t table (id int identity(1,1), val nvarchar(max))
as
begin

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

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

 union all

 select
  i = cte.i + 1,
  s = cte.m,
  n = substring(cte.m, 0, charindex(@splitChar, cte.m)),
  m = substring(
   cte.m,
   charindex(@splitChar, cte.m) + 1,
   len(cte.m)-charindex(@splitChar, cte.m)
 )
 from cte
 where i <= @j
)
insert into @t (val)
select pieces
from
(
 select
 ltrim(rtrim(case when i <= @j then n else m end)) pieces
 from cte
) t
where
 len(pieces) > 0
option (maxrecursion 0)

return

end

GO




Follow Kodyaz on Twitter

Related SQL Resources

SQL Server Articles

SQL Server 2012

SQL Server Tools

SQL Blog

MS SQL Server Forums









Copyright © 2004 - 2015 Eralper Yilmaz. All rights reserved.
Community Server by Telligent Systems