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:
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:
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:
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:
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:
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:
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:
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:
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:
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:
[ALSO READ]
Awesome article.
I enjoyed reading this article and appreciate for educating the community.
Please keep writing such articles.
Thanks a lot
Thank you Kris
Thanks a lot Kris