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))
INSERT INTO StudentClasses
SELECT 1, 'Mark', 'Maths,Science,English'
SELECT 2, 'John', 'Science,English'
SELECT 3, 'Robert', 'Maths,English'
FROM StudentClasses

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

SELECT ID, Student, value 
FROM StudentClasses 

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

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)

CSV, SQL Function, SQL Scripts, SQL Server, SQL Server 2016
Previous Post
SQL SERVER – How to Get SQL Server Agent Properties?
Next Post
SQL SERVER – Restore Database Wizard in SSMS is Very Slow to Open

Related Posts

5 Comments. Leave new

  • Rajeev Raman
    July 8, 2018 12:43 am

    Can we create same function by copying from SQL server 2016 or higher to SQL server version lower than 2016.

  • Hi Dave, excelent report.
    i have a question… this example only works if all records have a “,” comma delimiter in classes field. But in my example, some records dont have value in clases, in this case dont appears the result.

    how can i do?

  • Heelo, this function “STRING_SPLIT”, works in SQL Server 2008 r2 ??. Thanks

  • STRING_SPLIT() works fine, but does not honour the order in which the items appeared in the original string…

  • SQL Server 2016 and later


Leave a Reply

Exit mobile version