STRING_SPLIT function in Sql Server 2016

STRING_SPLIT is one of the new built-in table valued function introduced in Sql Server 2016. This table valued function splits the input string by the specified character separator and returns output as a table.

SYNTAX:

STRING_SPLIT (string, separator)

Where string is a character delimited string of type CHAR, VARCHAR, NVARCHAR and NCHAR.
Separator is a single character delimiter by which the input string need to be split. The separator character can be one of the type: CHAR(1), VARCHAR(1), NVARCHAR(1) and NCHAR(1).
Result of this function is a table with one column with column name as value.

Let us understand this function with extensive list of examples:

EXAMPLE 1: This example shows how we can use STRING_SPLIT function to splits the comma separated string.

SELECT * 
FROM STRING_SPLIT('Basavaraj,Kalpana,Shree',',')

RESULT:
Sql STRING_SPLIT Function Example 1

EXAMPLE 2: In this example, passing string and separator parameters as variables.

DECLARE @string VARCHAR(100) = 'Basavaraj,Kalpana,Shree',
        @separator CHAR(1) =',' 
SELECT * 
FROM STRING_SPLIT(@string,@separator)

RESULT:
Sql STRING_SPLIT Function Example 2

Let us execute the following statement which is moving the split result into a temp table. After inserting the split result into the temp table we are verifying the split result column type by using the sp_columns system stored procedure.

DECLARE @string VARCHAR(100) = 'Basavaraj,Kalpana,Shree',
        @separator CHAR(1) =',' 
SELECT *
	INTO #TempTable
FROM STRING_SPLIT(@string,@separator)
GO
TEMPDB..sp_Columns #TempTable

RESULT:
Sql STRING_SPLIT Function Example 21

From the result we can conclude that the size of the value column in the table returned by the STRING_SPLIT function will be same as that of the string which needs to split and type will be VARCHAR if the string which needs to be split is of type CHAR or VARCHAR.

EXAMPLE 3: This example demonstrates that the separator can be of only one character.

DECLARE @string VARCHAR(100) = 'Basavaraj#@Kalpana#@Shree',
        @separator CHAR(2) ='#@' 
SELECT * FROM STRING_SPLIT(@string,@separator)

RESULT:

Msg 214, Level 16, State 11, Line 3
Procedure expects parameter ‘separator’ of type ‘nchar(1)/nvarchar(1)’.

Let us re-try this example by using a single character separator instead of two characters

DECLARE @string VARCHAR(100) = 'Basavaraj#@Kalpana#@Shree',
        @separator CHAR(2) ='@' 
SELECT * FROM STRING_SPLIT(@string,@separator)

RESULT:
Sql STRING_SPLIT Function Example 3

From the above example results it is clear that the separator should always be a single character of any one of the data type CHAR(1), VARCHAR(1), NVARCHAR(1) and NCHAR(1).

EXAMPLE 4: In this example both the string which needs to be split and the separator are double byte characters.

DECLARE @string NVARCHAR(100) = N'你好中你好',
        @separator NCHAR(1) =N'中' 
SELECT * FROM STRING_SPLIT(@string,@separator)

RESULT:
Sql STRING_SPLIT Function Example 4

Let us execute the following statement which is moving the split result into a temp table. After inserting the split result into the temp table we are verifying the split result column type by using the sp_columns system stored procedure.

DECLARE @string NVARCHAR(100) = N'你好中你好',
        @separator NCHAR(1) =N'中' 
SELECT * 
	INTO #TempTbl
FROM STRING_SPLIT(@string,@separator)
GO
TEMPDB..sp_Columns #TempTbl

RESULT:
Sql STRING_SPLIT Function Example 41

From the result we can conclude that the size of the value column in the table returned by the STRING_SPLIT function will be same as that of the string which needs to split and type will be NVARCHAR if the string which needs to be split is of type NCHAR or NVARCHAR.

EXAMPLE 5: This example explains the behavior of the STRING_SPLIT function if the parameters are null

Let us execute the following statement where the Separator is NULL

SELECT * FROM STRING_SPLIT('Basavaraj,Kalpana,Shree',NULL)

RESULT:

Msg 214, Level 16, State 11, Line 1
Procedure expects parameter ‘separator’ of type ‘nchar(1)/nvarchar(1)’.

Let us execute the following statement where the string which needs to be split is NULL.

SELECT * FROM STRING_SPLIT(NULL,',')

RESULT:
Sql STRING_SPLIT Function Example 5

EXAMPLE 6: In this example we are splitting the string and the resultant table is joined with another table

Let us create a customer table with sample records by executing the following script

--Create customer table
CREATE TABLE Customer 
( CustomerId INT IDENTITY (1, 1) NOT NULL ,
  FirstName NVARCHAR(50), LastName NVARCHAR(50))
GO
--Insert 3 sample records into the customer table 
INSERT INTO dbo.Customer ( FirstName, LastName )
VALUES ('Basavaraj','Biradar'),
       ('Kalpana','Patil'),
       ('Shree','Biradar')

Execute the below state to split the string and the resultant table is joined with another table

SELECT *
FROM dbo.Customer C 
		INNER JOIN STRING_SPLIT('Basavaraj,Shree',',') SF
			ON C.FirstName = SF.value

RESULT:
Sql STRING_SPLIT Function Example 6

EXAMPLE 7: This example explains the behavior of the STRING_SPLIT function if the string to be split is an empty string

SELECT * FROM STRING_SPLIT('',',')

RESULT:
Sql STRING_SPLIT Function Example 7

EXAMPLE 8: This example shows the behavior of the STRING_SPLIT function if the string to be split is terminated by the separator character

SELECT * FROM STRING_SPLIT('Basavaraj,Shree,',',')

RESULT:
Sql STRING_SPLIT Function Example 8
[ALSO READ]

SQL SERVER 2016

6 thoughts on “STRING_SPLIT function in Sql Server 2016

  1. Awesome article.

    I enjoyed reading this article and appreciate for educating the community.

    Please keep writing such articles.

    Thanks a lot

Leave a Reply

Your email address will not be published. Required fields are marked *