SQL SERVER – Split Comma Separated Value String in a Column Using STRING_SPLIT

May 4, 2018

In this blog post, we will learn about STRING_SPLIT function which was earlier introduced in SQL Server 2016 but still not widely adopted in the industry. We will learn today how to Split Comma Separated Value String in a Column Using STRING_SPLIT.

SQL SERVER - Split Comma Separated Value String in a Column Using STRING_SPLIT split_string_example0

For example, if you have following resultset and if you want each of the value of the comma separated string from the last column in a separate row, previously you had to use a very complicated function on the column which was a performance killer.

SQL SERVER - Split Comma Separated Value String in a Column Using STRING_SPLIT split_string_example2

Now we can just STRING_SPLIT function and can achieve that very quickly. Let us understand with a simple example.

First, let us create a sample data.

CREATE TABLE StudentClasses
(ID INT, Student VARCHAR(100), Classes VARCHAR(100))
GO
INSERT INTO StudentClasses
SELECT 1, 'Mark', 'Maths,Science,English'
UNION ALL
SELECT 2, 'John', 'Science,English'
UNION ALL
SELECT 3, 'Robert', 'Maths,English'
GO
SELECT *
FROM StudentClasses
GO

Next, let us write a code which will split command separate value string in a column.

SELECT ID, Student, value 
FROM StudentClasses 
 CROSS APPLY STRING_SPLIT(Classes, ',')
GO

That’s it. We are done. It is a very simple task with the help of new function. You can further clean up your sample data by running following command.

SQL SERVER - Split Comma Separated Value String in a Column Using STRING_SPLIT split_string_example1