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 SQL Server and T-SQL Development Tutorials
Development resources, articles, tutorials, code samples and tools and downloads for ASP.Net, SQL Server, R Script, Windows, Windows Phone, AWS, SAP HANA and ABAP, like SAP UI5, Screen Personas, etc.




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



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.

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

END -- function
GO

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))
AS
BEGIN

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

RETURN
END
GO

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






Related SQL Resources

SQL Server Articles

SQL Server 2012

SQL Server Tools

SQL Blog

MS SQL Server Forums







Copyright © 2004 - 2019 Eralper YILMAZ. All rights reserved.
Community Server by Telligent Systems