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.

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

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)

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

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

    Reply
  • 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?
    Thanks

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

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

    Reply
  • SQL Server 2016 and later

    Reply
  • Thank you. It’s working fine

    Reply
  • Kalyana Sundafam
    July 2, 2024 8:51 pm

    How to handle an column value in a comma separated string?

    Reply

Leave a Reply