How to Use Split String with SQL Server

Had a column of data in a table that was comma separated. The data need to be separated so that it could be used to populate another table with it's information. Consulting with ChatGPT, it provided an example and I attempted to use it. However, that example was wrong and did not work.

I did some research using Google and found out the proper way to use the string_split function.

How Not To Use

With some functions, you can return the results as a column. Thus at first I tried this... 

select string_split(my_column)
from my_table

As a result, I would get this error message

Msg 195: STRING_SPLIT is not a recognized built-in function name

I will admit, this error message is a bit misleading. From reading it, you would think that the function did not exist. However, it does exist, but in this example, it is being used incorrectly.

What I found was that you have to be running SQL Server 2016 or newer. Old version do not have this method built in, and thus would have to manually create it in order to use it. There were some additional queries that were provided by the Microsoft documentation to find what version of SQL Server is being used.

After searching for more examples and reading the Microsoft documentation, I was able to find the correct way to use the function.

How To Use

Since the string_split takes a single column of data and separates into rows, the query should look like the below. 

select *
from my_table
    cross apply string_split(my_column, ',')

What this does is to take the column and split each of the contents into its own row in the result set. Also, each of the values that are return from string_split, are in a column called "value". Because of this, I had to modify the query so that the column name was meaningful for the database adminstrators that would be running the query. Thus the final version of the query looked like 

select *, value as MySplitColumn
from my_table
    cross apply string_split(my_column, ',')

By doing this, each of the values from my_column will have a value in MySplitColumn column.

Follow Us