Split Comma Separated String Using STRING_SPLIT in SQL Server

watch_later 8/24/2019


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
Split Comma Separated String Using STRING_SPLIT

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.


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.


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.


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', ',');



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 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'.


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.

Codingvila 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.

Sometimes I not able to give detailed level explanation for your questions or comments, if you want detailed explanation, your can mansion your contact email id along with your question or you can do select given checkbox "Notify me" the time of write comment. So we can drop mail to you.

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

sentiment_satisfied Emoticon