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

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.

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.

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.

DROP TABLE StudentClasses
GO

Remember, if you ever receive following error:

Msg 208, Level 16, State 1, Line 7
Invalid object name ‘STRING_SPLIT’.

I suggest you read this blog post for the solution of above error. This function will only work if you have compatibility level to 2016 or later.

Reference: Pinal Dave (https://blog.sqlauthority.com)