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)
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
Thank you. It’s working fine