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.
