SQL Server 2016 STRING_SPLIT Function

By:   |   Comments (15)   |   Related: 1 | 2 | 3 | 4 | 5 | 6 | > Functions System


Problem

During your experience as a SQL Server Database Administrator, you may work with applications that store data as comma-separated values in a single column. To deal with this de-normalized data, you may need to normalize it to work with each single value separately in your SQL Server code or provide this normalized data to another data source. We used to write such user defined functions that takes a string, loops through it, searching for the delimiter and retrieve a table with these separated values. What is hidden for us in SQL Server 2016 to achieve this task more efficiently?

Solution

SQL Server 2016 introduced a new built-in table-valued function, STRING_SPLIT that splits the provided input string by a specified separation character and returns the output separated values in the form of table, with a row for each delimited value between each separator character.

STRING_SPLIT function takes two parameters:

STRING_SPLIT ( string , separator )

The string is an expression of characters with char, nchar, varchar or nvarchar data types. The separator is a single character that will be used to separate the concatenated input string, with char, nchar, varchar or nvarchar data types.

STRING_SPLIT returns a single column table. The name of the returned column is value. The default data type of the value column is varchar. If the input string data type is nvarchar or nchar, the value column data type will be nvarchar. The length of the value column will be the same as the length of the string that will be split.

Database Compatibility Level Error for STRING_SPLIT Function

Before using the STRING_SPLIT function, you should make sure that the database compatibility level is 130. In my case, my local SQL Server instance was upgraded from SQL Server 2014 version to SQL Server 2016 version. So that, if I try to run the below simple straight-forward call for the STRING_SPLIT function on my database, providing it with a string to be separated and a comma as a separator:

SELECT * FROM STRING_SPLIT('John,Jeremy,Jack',',')
GO
   

The query will fail showing that this function is not available. This is expected in my case, as the database compatibility level will not be changed to the new version automatically when we upgrade the current SQL Server instance:

Database Compatibility Level Error for STRING_SPLIT Function

In order to be able to use the STRING_SPLIT function, we should change the compatibility level of the database to 130 using the below ALTER DATABASE T-SQL statement:

USE [master]
GO

ALTER DATABASE [MSSQLTipsDemo] SET COMPATIBILITY_LEVEL = 130
GO
   

Now the compatibility level of the database is modified to 130 to use the STRING_SPLIT function. Running the same SELECT statement again:

SELECT * FROM STRING_SPLIT('John,Jeremy,Jack',',')
   

You will see clearly that the STRING_SPLIT loops through the provided string, searches for the comma inside that string and returns a row for each separated value as shown below:

STRING_SPLIT loops through the provided string, searches for the comma inside that string and returns a row for each separated value

WHERE Clause with the STRING_SPLIT Function

The result that is returned from the STRING_SPLIT function can be filtered using the WHERE clause and ordered using the ORDER BY clause. The previous result can be sorted using the T-SQL below statement:

SELECT * FROM STRING_SPLIT('John,Jeremy,Jack',',') ORDER BY value asc
   

The sorted result will be like:

WHERE Clause with the STRING_SPLIT Function

Pass Parameters into the STRING_SPLIT Function

STRING_SPLIT parameters can also be passed as variables with different values. In the below script, two variables are declared; one to store the string to be delimited and the second one for the separator value:

DECLARE @String VARCHAR(50) = 'John,Jeremy,Jack', @Delimiter CHAR(1) =','
SELECT * FROM STRING_SPLIT(@String,@Delimiter)
   

Executing the previous query, the same result will be returned as follows:

Pass Parameters into the STRING_SPLIT Function

Separator Character for STRING_SPLIT Function

As mentioned previously, the separator is a single character that is used by the function as a separation criteria. If you try to separate the input string using two characters separator:

DECLARE @String VARCHAR(50) = 'John*$Jeremy*$Jack', @Delimiter CHAR(2) ='*$'
SELECT * FROM STRING_SPLIT(@String,@Delimiter)
   

The function will fail, showing that it should be always a single character of the CHAR(1), VARCHAR(1), NVARCHAR(1) and NCHAR(1) datatypes:

Separator Character for STRING_SPLIT Function

Defining the separator as a single character:

DECLARE @String VARCHAR(50) = 'John*$Jeremy*$Jack', @Delimiter CHAR(1) ='*'
SELECT * FROM STRING_SPLIT(@String,@Delimiter)
   

The result will be like:

Result Set with Separator Character for STRING_SPLIT Function

Unicode Separator for STRING_SPLIT Function

Both the string that will be delimited and the separator can take Unicode values by defining the string as NVARCHAR and the delimiter as NCHAR. In the following example, a string written in Arabic language will be separated by an Arabic character:

DECLARE @String NVARCHAR(50) = N'ÃÍãÏ æ ãÍãÏ æ ãÄíÏ', @Delimiter NCHAR(1) =N'æ'
SELECT * FROM STRING_SPLIT(@String,@Delimiter)   

The separated Arabic values will be like:

Unicode Separator for STRING_SPLIT Function

NULL Separator for STRING_SPLIT Function

The separator also cannot be passed as NULL, if you define the delimiter value as NULL:

DECLARE @String VARCHAR(50) = 'John,Jeremy,Jack', @Delimiter CHAR(1) = NULL
SELECT * FROM STRING_SPLIT(@String,@Delimiter)
   

The function will fail again showing the same previous error:

NULL Separator for STRING_SPLIT Function

The input string is another case. If you try to pass the string to be delimited as NULL:

DECLARE @String VARCHAR(50) = NULL, @Delimiter CHAR(1) =','
SELECT * FROM STRING_SPLIT(@String,@Delimiter)
   

The function will not fail, but an empty table will be returned from the function as below:

Null Values with STRING_SPLIT Function

If no value is specified after the delimiter as in this script:

DECLARE @String VARCHAR(50) = 'John,Jeremy,', @Delimiter CHAR(1) =','
SELECT * FROM STRING_SPLIT(@String,@Delimiter)
   

This unspecified value will be treated as an empty string with empty value returned as a separate row and not NULL value, as shown in the result below:

STRING_SPLIT Function with Null Input

Clean De-Normalized Data with the STRING_SPLIT Function

SQL Server Cursor with STRING_SPLIT Function

The STRING_SPLIT function will be useful also in specific cases, in which you need to clean de-normalized data in a specific table and insert it into another table to use it there. In order to use the STRING_SPLIT function to parse the data in the table row by row, the first method appears in our minds to do that is using the cursors. The below script defines a cursor that reads the ProductID and the Name from the Product table, and separates the value of each name using the space delimiter and insert the generated result into a temp table defined at the beginning of the script:

USE MSSQLTipsDemo GO
CREATE TABLE #TempSubProduct(ID int , SubName varchar(50))
DECLARE @PID INT
DECLARE @Name VARCHAR(100)
DECLARE Split_Product CURSOR LOCAL FAST_FORWARD FOR 
        SELECT ProductID , Name
        FROM Production.Product 
 
OPEN Split_Product
 
FETCH NEXT FROM Split_Product 
INTO @PID, @Name
 
WHILE @@FETCH_STATUS = 0
BEGIN

INSERT INTO #TempSubProduct
        SELECT @PID, SPL.value
        FROM STRING_SPLIT(@Name,' ') AS SPL
 
        FETCH NEXT FROM Split_Product 
        INTO @PID, @Name
END 
 
SELECT * FROM #TempSubProduct
DROP TABLE #TempSubProduct
   

A sample of the generated result in our case will be like:

SQL Server Cursor with STRING_SPLIT Function

SQL Server CROSS APPLY with STRING_SPLIT Function

The same previous process can be performed by replacing the cursor by the CROSS APPLY method as follows:

USE MSSQLTipsDemo
GO

CREATE TABLE #TempSubProduct(ID int , SubName varchar(50))

INSERT INTO #TempSubProduct (ID, SubName)
SELECT ProductID, SPL.value
FROM Production.Product  AS PP
CROSS APPLY STRING_SPLIT(PP.Name,' ') AS SPL;

SELECT * FROM #TempSubProduct

DROP TABLE #TempSubProduct
   

With the same returned result that will be like:

SQL Server CROSS APPLY with STRING_SPLIT Function

SQL Server JOIN with STRING_SPLIT Function

Another use for the STRING_SPLIT function is to find specific rows in a table. What you need to do is to pass a specific column values to the STRING_SPLIT function as the string to be separated and join the main table with the STRING_SPLIT function result. In the below script, we pass a list of comma-separated IDs from the Product table and join it with the Product table to retrieve the name of products with these IDs from the main table:

USE MSSQLTipsDemo
GO

SELECT PP.ProductID, PP.name, SPL.value
FROM Production.Product PP
JOIN STRING_SPLIT('316,317,318,319,320,321,322,323,324,325,326,327,328,329,330', ',') AS SPL
ON SPL.value = PP.ProductID
   

And the results will look like:

SQL Server JOIN with STRING_SPLIT Function

User Defined STRING_SPLIT Function Example

To encourage ourselves to use the STRING_SPLIT built-in function to replace the legacy functions we used to split string values, we will create a user-defined split function.  Here is one example:

USE MSSQLTipsDemo
GO

CREATE FUNCTION [dbo].[SplitString]
(
    @String NVARCHAR(4000),
    @Delimiter NCHAR(1)
)
RETURNS TABLE
AS
RETURN
(
    WITH Split(stpos,endpos)
    AS(
        SELECT 0 AS stpos, CHARINDEX(@Delimiter,@String) AS endpos
        UNION ALL
        SELECT endpos+1, CHARINDEX(@Delimiter,@String,endpos+1)
            FROM Split
            WHERE endpos > 0
    )
    SELECT 'Id' = ROW_NUMBER() OVER (ORDER BY (SELECT 1)),
        'Value' = SUBSTRING(@String,stpos,COALESCE(NULLIF(endpos,0),LEN(@String)+1)-stpos)
    FROM Split
)
   

Once the split function is created successfully, let us try to separate a specific string twice, the first time using the created user-defined split function and the second time using the built-in STRING_SPLIT function, enabling the time statistics and the actual execution plan to have a reasonable comparison:

USE MSSQLTipsDemo
GO

SET STATISTICS TIME ON

DECLARE @String VARCHAR(100) = 'John,Jeremy,Jack,Ali,Mohammed,Ahmad,Zaid,Kenan,Kinda,Saed',
        @Delimiter CHAR(1) =','

SELECT * FROM DBO.SplitString(@String,@Delimiter)
GO

DECLARE @String VARCHAR(100) = 'John,Jeremy,Jack,Ali,Mohammed,Ahmad,Zaid,Kenan,Kinda,Saed',
        @Delimiter CHAR(1) =','

SELECT * FROM STRING_SPLIT(@String,@Delimiter)
GO

SET STATISTICS TIME OFF
   

Both functions returned the same result as follows:

User Defined STRING_SPLIT Function Example

But the important question is, how much it takes each function to return the result? From the time statistics, the user-defined split function took 29 ms to separate the defined string, where it took the STRING_SPLIT built-in function 0 ms to separate the same string.

Execution Times for STRING_SPLIT Function

The performance difference is also clear by comparing the execution plans generated by each run. The weight of the user defined split function execution plan is about 5 times the weight of the STRING_SPLIT execution plan as shown below:

Query Plans for STRING_SPLIT Function

I encourage you to start using the new STRING_SPLIT function, one of the new enhancements in SQL Server 2016.

Next Steps


sql server categories

sql server webinars

subscribe to mssqltips

sql server tutorials

sql server white papers

next tip



About the author
MSSQLTips author Ahmad Yaseen Ahmad Yaseen is a SQL Server DBA with a bachelor’s degree in computer engineering as well as .NET development experience.

This author pledges the content of this article is based on professional experience and not AI generated.

View all my tips



Comments For This Article




Thursday, January 27, 2022 - 1:39:57 PM - Jeff Moden Back To Top (89705)
@PITREL - That's NOT a "bug" in String_Split()... that's a bug in your logic.

Wednesday, April 22, 2020 - 7:47:27 PM - Chandra Sekhar Back To Top (85449)

Here is another way:

-- Here is the String Array you want to convert to a Table
declare @StringArray varchar(max)
set @StringArray = 'First item,Second item,Third item';

-- Here is the table which is going to contain the rows of each item in the String array
declare @@mytable table (EachItem varchar(50))

-- Just create a select statement appending UNION ALL to each one of the item in the array
set @StringArray = 'select ''' + replace(@StringArray, ',', ''' union all select ''') + ''''

-- Push the data into your table
insert into @@mytable 

Friday, April 17, 2020 - 12:05:32 PM - PITREL Back To Top (85404)

There is a magnificent bug in string_split !

DECLARE @String VARCHAR ( 50 ) = 'John, Jeremy, Jack' 
DECLARE @Delimiter CHAR(1) = ','
SELECT value FROM STRING_SPLIT ( @String , @Delimiter ) 
 
-- There is a dangerous bug in string_split...
-- 1
SELECT 1 sample, value FROM STRING_SPLIT ( @String , @Delimiter ) 
WHERE value = 'Jeremy'
-- return 0 row !

-- 2
SELECT 2 Sample, ltrim(rtrim(value)) as value_trimed FROM STRING_SPLIT ( @String , @Delimiter ) 
WHERE value = 'Jeremy'
-- return 0 row !

-- 3
SELECT 3 sample, ltrim(rtrim(value)) as value_trimed_but_One_space FROM STRING_SPLIT ( @String , @Delimiter ) 
WHERE value = ' Jeremy'
--  one space > < before firstname clause
--One row.. but paradoxal result by clause


-- dangerous sample 
-- this query will never return any row !!! 
Select Firstname, Lastname, phone, adress from myTable
WHERE Lastname in (SELECT value FROM STRING_SPLIT ( @String , @Delimiter ))

Friday, January 10, 2020 - 5:32:12 AM - dhivakar Back To Top (83667)

Product.Group.Code.is.blank.value target is to break this into individual words and find the length of each word using substring query.

could you please let me know


Tuesday, June 4, 2019 - 6:56:44 AM - Ahmad Yaseen Back To Top (81320)

Hello Kelly,

Please try to use intermediate temp table to save the result of the split function and before filling the data into the 6 columns.

Best Regards,


Monday, May 27, 2019 - 4:15:10 AM - Kelly Nare Back To Top (81216)

Hi hope you are well, 

My version of MSSQL does not allow for the use of the string_split function and I would need to hard code the function which is not a problem. 

My issue is looping the function through a string column and saving the results into columns, 6 columns to be exact.

The string column is as follws (one row example):

_234_55_2025/10/15__546-00_45_2025/10/15

I am splitting on the underscore and I would like each result to be saved in its own column.

Please help

Kelly


Wednesday, September 19, 2018 - 11:05:23 AM - Matthias Back To Top (77657)

This one does the trick:

select ROW_NUMBER() over (order by (SELECT NULL)) as RN,value from string_split('aaa:bbb:ccc',':')


Monday, August 20, 2018 - 12:30:10 AM - kartheek Back To Top (77241)

  I/P: '123.56,45.873,4.5,4.0,45768.9,354.67,12.0,66.97,45,4.5672',',')

Output want to be like below without using STRING_SPLIT function, we are using 2008 R2 version

Please might me help out

   O/P:

                      123.56

                       45.873

                        4.5

                       4.0

                      45768.9

                      354.67

                     12.0

                      66.97

                     45

                     4.5672

Thank You


Wednesday, August 1, 2018 - 3:18:36 AM - VINOD H PATIL Back To Top (76923)

 The better way to get row number on the same row is as below

SELECT ROW_NUMBER() OVER (ORDER BY DT), VALUE FROM

(select value,GETDATE() DT from STRING_SPLIT('empname_empcode_PANNo_birthdateDD/MM/YYYY' ,'_')) A

 

This will return 

"COLUMN1" "VALUE"

"1" "empname"

"2" "empcode"

"3" "PANNo"

"4" "birthdateDD/MM/YYYY"

 

 


Thursday, June 7, 2018 - 8:45:38 AM - Ahmad Yaseen Back To Top (76147)

Please note that this is available starting from SQL 2016. Check the comoatability level of the database. If your database compatibility level is lower than 130, SQL Server will not be able to find and execute STRING_SPLIT function.

 

 Best Regards,

Ahmad


Thursday, June 7, 2018 - 6:18:51 AM - shrikant Back To Top (76144)

Hi Ahmad, we are unable to use STRING_SPLIT (Split comma-separated value string) SQL server 2016 Please reconfirm with Version and services Pack.

Regards, Shrikant Verma


Sunday, March 18, 2018 - 5:35:50 AM - Ahmad Yaseen Back To Top (75457)

Hello Dear,

As i know, linked servers and functions are not friends. Try to get the data you need to process using the functin in a temp table then process it locally.

 

Best Regards,

Ahmad 

 


Friday, March 16, 2018 - 11:08:58 PM - Andrew Back To Top (75453)

 

Hi Ahmad,

Do you know if the string_split function can be run against a MySQL linked server from within SQL?

Regards,

Andrew


Sunday, May 28, 2017 - 1:53:38 AM - Ahmad Yaseen Back To Top (56136)

 Hi Kingston,

 

Unfortunately, the code of the built-in function is not available, but regarding my experience with such functions, the user defined function provided in the article is the best one I have tried with the best performance (from the ones I have tried). But the built-in function is available in SQL Server 2016 only.

 Best Regards,

Ahmad


Friday, May 26, 2017 - 5:45:39 AM - Kingston Back To Top (56082)

 Hi Ahmad,

              Can the code of that inbuilt function used as an UDF in any lower version of SQL Server ? (And get the same performance as the inbuilt ?) 

 

Regards,

Kingston

 















get free sql tips
agree to terms