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:
https://learn.microsoft.com/en-us/sql/t-sql/functions/string-split-transact-sql?view=sql-server-ver16
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:
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:
