This week I have been practising my SQL skills with the Week 2 Case Pizza Run in 8 Week SQL Challenge. In this blog I would like to introduce you to the function of STRING_SPLIT to split a column into rows (with MS SQL Server).

When preparing the data for analysis, we sometimes come across a column with all the ids concatenated in one column. In order to be able to further joined with the dimension table based on the ID, we need to split the string to rows to create the foreign key. This requires us to split the column to rows, which can be achieved by the function STRING_SPLIT() and it requires the database complexity level to be at least 130. The documentation of the function and how to change the database complexity level can be found in the following link:

Now we are going to look at two different cases to use this function.

I. Split a Single String
With this function, we can split a string into rows with a given delimiter. For example, if we need to split the string ‘1,2,3,4,5,6,7’ into rows, it can be achieved by the following function:

SELECT VALUE AS split_result
FROM STRING_SPLIT(‘1,2,3,4,5,6,7′,’,’)

This code splits the input string ‘1,2,3,4,5,6,7’ into individual values by delimiter comma, and then selects those values, with each value being assigned the alias “split_result” in the output. The result will be a table with one column named “split_result” containing the values 1, 2, 3, 4, 5, 6, and 7. The result is shown as follows:

II. Spliting a Column with Multiple Rows

If we need to split a column with multiple rows in it, the code needs to be configured a bit differently. For example, if we have the following table called test:

We can split the columns into rows with the following code:

FROM test
    STRING_SPLIT(Original_String, ‘,’)
The result is shown as follows:
Then with each ID in seperate rows, we will be able to further join with dimension tables for further analysis. If you are interested in the 8 Week SQL Challenge, here is the link to it:
The Data School
Author: The Data School