Split Comma Separated String Using STRING_SPLIT in SQL Server

watch_later Saturday, August 24, 2019

Introduction


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.

Requirement 


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.

Syntex

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.

Example 


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

Result

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

Expected Result

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

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

Summary


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.

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.



sentiment_satisfied Emoticon