SQL Server | Split Comma Separated String Using STRING_SPLIT

watch_later Saturday, August 24, 2019
This article gives an explanation about how to split comma separated string using 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 string in sql server using XML in my previous article, but still many of developers and students request me by mail to share more article 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 string in sql using 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. 
Split Comma Separated String Using STRING_SPLIT

While you working with data-driven application sometimes you have delimited string as input and you need data in a separate row and for that, you need to split your input string by your delimiter like comma (','), ('|'), ('\'),(';'),('@'),('^'),('~') as well as single quotes, double quotes and etc.


1) What is STRING_SPLIT function?
2)What is the syntax of 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 table-valued function introduced in sql server 2016 witch is used for splits a delimited string into a table based on the specified separator.


STRING_SPLIT ( input_string , separator )  
As you can see in the syntax whare input_string is your delimited string and separator accepts a single character for split input_string.


So, let's start with an example so you can get more idea about 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', ',');



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 (
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)
Select Records From Table

Expected Result

Expected Result

Query to Generate Expected Result

SELECT Designationid, Designation, EmployeeName 
FROM Designation_Master WITH (NOLOCK)


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.


Split String Using STRING_SPLIT

Important Note

STRING_SPLIT() function is introduced in SQL Server 2016 so this function is only work 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'.


This article explains how to split delimited string in sql server using STRING_SPLIT() function as well as the use of this function in sql server 2016.

Codingzee provides articles and blogs on web and software development for beginners as well as free Academic projects for final year students in Asp.Net, MVC, C#, Vb.Net, SQL Server, Angular Js, Android, PHP, Java, Python, Desktop Software Application and etc.

Thank you for your valuable time, to read this article, If you like this article, please share this article and post your valuable comments.

Once, you post your comment, we will review your posted comment and publish it. It may take a time around 24 business working hours.

If you have any questions regarding this article/blog you can contact us on codingzee@gmail.com

sentiment_satisfied Emoticon