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