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
Development resources, articles, tutorials, code samples, tools and downloads for ASP.Net, SQL Server, Reporting Services, T-SQL, Windows, AWS, SAP HANA and ABAP

User Defined SQL Split String Function for Database Developer

SQL Server database developers frequently require a split string function during their SQL code programming used in stored procedures or functions. Within kodyaz.com I have shared a number of solutions including SQL recursive CTE split functions, or SQL CLR split string functions to use on SQL Server databases. Although the current solution is the earliest solution developed by SQL community, since I want to take the solution from Forums section and publish it as a SQL tutorial for database developers, I create this database programming tutorial. The functions scalar split function and table-valued UDF SQL split string function shared here don't use recursive CTE expressions or are not CLR based SQL functions as well as not using SQL Server 2016 enhancement split_string function.

Scalar SQL Split Function returning Nth Piece

If you are looking for a scalar SQL split function which takes a string to be splitted, a delimeter character and an integer to identify the order of the splitted piece as the return value of the function, following SplitAndReturnNth SQL User Defined Function can be used.
Please note that this function uses a custom user-defined SQL numbers table dbo.NumbersTable

As noted within the source codes of the SQL function, the function splits @string input parameter using @delimiter character.
If input parameter @idx value is 0, the input @string value is returned back as the output of the function.
If @idx value is bigger than zero then after split the Nth string fragment is returned.
If there is not enough splitted pieces that will match the Nth part, the function returns NULL value.

 @string VARCHAR(200),
 @delimiter CHAR(1),
 @nth INT
-- @nth = 0 => return input @string parameter back
-- else return the Nth piece after split
-- if there is not N pieces after split, returns NULL
CASE @nth
 WHEN 0 THEN @string -- return input value
 ELSE -- return Nth string piece after split
 SELECT string
   SUBSTRING(@string, i, CHARINDEX( @delimiter, @string + @delimiter, i ) -i ),
   i + 1 - LEN(REPLACE(LEFT(@string, i), @delimiter, ''))
  FROM dbo.NumbersTable(1,100,1)
   SUBSTRING(@delimiter + @string, i, 1) = @delimiter
   AND i < LEN(@string)+ 1
 ) AS T(string, nth)
 WHERE nth = @nth -- Nth item
END -- case

END -- function

Here is a few sample cases for Transact-SQL developers to test on their development databases showing how this scalar SQL split string function can be used:

select dbo.SplitAndReturnNth('one,two,three,four',',',3) -- three
select dbo.SplitAndReturnNth('Vader,Luke,Leia,Han',',',9) -- NULL

Table Valued SQL Split String Function

The above T-SQL function can be easily converted into a table valued function which returns all the splitted string pieces with their order in the original string.
Please note the same SQL numbers table is used in this SQL function too.
But it is possible to use an older method that will work on every SQL Server version which query master..spt_values view.

-- Zero based numbers table query
SELECT number FROM master..spt_values
WHERE type = 'P' ORDER BY number

Here is our modified SQL split string function source code (using recursive CTE numbers table function).

CREATE FUNCTION SplitInputStringBy (
@string nvarchar(max),
@delimiter CHAR(1)
RETURNS @t TABLE(N int identity(1,1), String nvarchar(max))

INSERT @t(String)
 SUBSTRING(@string, i, CHARINDEX( @delimiter, @string + @delimiter, i ) -i )
FROM dbo.NumbersTable(1,100,1)
 SUBSTRING(@delimiter + @string, i, 1) = @delimiter
 AND i < LEN(@string)+ 1


Let's split our previous sample string values using table-valued SQL function and test results on a development database.

split string using SQL function

SQL Server

SQL Server 2019 Installation
download SQL Server 2019
download SQL Server 2017
download SQL Server 2016
download SQL Server 2014
download SQL Server 2012
MacOS ve SQL Server 2019

Copyright © 2004 - 2021 Eralper YILMAZ. All rights reserved.