Introduction
This article gives an explanation of how to split comma-separated strings using the STRING_SPLIT function and shows you how to split a delimited string and return a result set in a tabular manner.
I have also explained split comma-separated strings in SQL server using XML in my previous article, but still, many developers and students request me by mail to share more articles regarding this topic with optimized solutions so they can improve the performance of queries. So, in this article, I'll show how to split comma-separated strings in SQL using the table-valued function STRING_SPLIT which is introduced in SQL server 2016 and I will also share such pieces of information about the optimized and inbuilt functions in my upcoming articles.
In my previous article, I explained split comma separated string in SQL server using XML and how to fix the 'Microsoft.ACE.OLEDB.12.0' the provider is not registered on the local machine and how to use and create cursor in SQL server with syntax as well how to fix SQL server restore database error | alter database mode single user to multi-user.
While you working with a data-driven application sometimes you have delimited string as input and you need data in a separate row for that, you need to split your input string by your delimiter like comma (','), ('|'), ('\'),(';'),('@'),('^'),('~') as well as single quotes, double quotes and etc.
Requirement
1) What is the STRING_SPLIT function?
2)What is the syntax of the STRING_SPLIT function?
3) Prepare a sample table with data for demonstration.
4) Split a delimited string using STRING_SPLIT.
What is STRING_SPLIT() function?
STRING_SPLIT function is a table-valued function introduced in SQL server 2016 which is used for splitting a delimited string into a table based on the specified separator.
Syntex
STRING_SPLIT ( input_string , separator )
As you can see in the syntax where input_string is your delimited string and separator accepts a single character for split input_string.
Example
So, let's start with an example so you can get more idea about the STRING_SPLIT function.
Let's take a simple example where I have a comma-separated string and I want to spit that string and get a result in a tabular manner.
SELECT value AS EmployeeName FROM STRING_SPLIT('Nikunj Satasiya,Hiren Dobariya,Vivek Ghadiya,Krishna Patel', ',');
Result
Now, let us take another example where I want individual records for each employee in a tabular manner.
So first create a table with sample data.
Create Table
CREATE TABLE Designation_Master ( Designationid INT PRIMARY KEY IDENTITY, Designation VARCHAR(50) NOT NULL, EmployeeName VARCHAR(50) NOT NULL );
Insert Record
INSERT INTO Designation_Master (Designation,EmployeeName) VALUES ('Software Enginner','Nikunj Satasiya,Hiren Dobariya,Vivek Ghadiya,Krishna Patel'), ('Sales Manager','PriyaPatel, Harsh Savaliya'), ('Project Manager','Manish Korat, Pratik Pansuriya')
Select Records From Table
SELECT* FROM Designation_Master WITH (NOLOCK)
Expected Result
Query to Generate Expected Result
SELECT Designationid, Designation, EmployeeName FROM Designation_Master WITH (NOLOCK) CROSS APPLY STRING_SPLIT(EmployeeName, ',')
Explanation
As you can see in the above SQL query where STRING_SPLIT() function is used to split the EmployeeName and CROSS APPLY to join with the Designation_Master table.
Result
Important Note
STRING_SPLIT() function is introduced in SQL Server 2016 so this function only works in 2016 or later. If you trying to use this function in an older version of SQL Server such as SQL Server 2005, 2008 and etc then you may get the following error.
Msg 208, Level 16, State 1, Line 41
Invalid object name 'STRING_SPLIT'.
Summary
This article explains how to split delimited strings in SQL server using the STRING_SPLIT() function as well as the use of this function in SQL server 2016.
