STRING_SPLIT, a table-valued function (TVF), splits delimited list into a single column table rows. SQL Server 2016 added STRING_SPLIT function, simplifying the problem of splitting rows, delimited by a separator into multiple rows of single values. Furthermore, Normalization Forms never recommends storing a delimited row in a table. In this case, a denormalized table can be converted to normalized one using STRING_SPLIT function.
While, former methods to split delimited list using cursors, XML methods, CTE s involved writing longer code and performed poorly on large data set. In short, STRING_SPLIT function is a go-to way to work with the delimited list going ahead with SQL Server 2016.
STRING_SPLIT (String, Delimiter)
String - Delimited list of string type.
Delimiter - Single character value act as a separator between delimited string.
STRING_SPLIT Function – Points To Ponder
1.STRING_SPLIT accepts only Single character delimiter. Parsing a delimited list involving multi-character delimiters is not permitted by design. Therefore, we can split a list separated by ‘,’ (Comma) but not like ‘,|’ (Comma-Pipe).
2.Value is default column name for result set generated by STRING_SPLIT function.
3.STRING_SPLIT function works with SQL Server 2016 and later editions. Make sure you are on SQL Server database with compatibility 130 or above using below query. Otherwise, you will find an error Invalid object name ‘STRING_SPLIT’.
Check Database Compatibility Level
STRING_SPLIT – Examples
Demonstrating STRING_SPLIT function with a few helpful examples here.
STRING_SPLIT – Split Delimited List In a Variable
@PlayerNames variable stores the list of player names separated by a comma delimiter. Furthermore, using the following query, we are splitting comma delimited list into a single column table with multiple rows. Most importantly, notice the returned table has a default column name value return by STRING_SPLIT table-valued function.
STRING_SPLIT – Split Delimited List In a Single Column
@Players, table variable store the list of player names separated by a pipe ‘|’ delimiter. In the following query, using CROSS APPLY operator to work with STRING_SPLIT table-valued function. APPLY operators are specially designed to work with TVFs as simple JOINs don’t work. Passing the Name column name and pipe delimiter as arguments to STRING_SPLIT function.
STRING_SPLIT – Split Delimited List In a Multiple Columns
In the following query, the @Records table has got two columns. Player names and their list of won trophies stored as comma separated values. Using STRING_SPLIT function we convert trophy names into a single column and associating it with player name.
STRING_SPLIT – Split Delimited List and WHERE Clause
Reusing the previous query, and adding a WHERE clause to filter out the records to fetch rows only for player name Federer.
STRING_SPLIT – Split Delimited List and IN Clause
In the following query, here STRING_SPLIT splits comma-delimited list and adding IN clause to filter out the rows based on value column returned by STRING_SPLIT function.
6. STRING_SPLIT – Split Delimited List and ORDER BY Clause
In particular, STRING_SPLIT doesn’t sort the return values from a delimited list. If you wish to sort the delimited rows then specify ORDER BY clause explicitly instead of relying on the default order returned by the query.
In the following query, we have added ORDER BY clause on TrophyName column to sort. Most importantly notice that the use of TrophyName column in ORDER BY instead of value column. In this case, Logical Processing Order in SQL Server allows us to refer the alias column name in ORDER BY clause. ORDER BY clause is evaluated after SELECT, therefore allowing us to use TrophyName in ORDER BY clause.
STRING_SPLIT – Split Delimited List and GROUP BY Clause
Following query demonstrates the use of GROUP BY clause with STRING_SPLIT function. First, separating the delimited list into multiple rows and counting the trophy names grouped by Name column.
STRING_SPLIT – Split Delimited List and INNER JOIN
Below query demonstrates the use of INNER JOIN with STRING_SPLIT function. Joining the Country column from @Venue table with value column returned by STRING_SPLIT function based on trophy name.
To summarize, we have learned to use STRING_SPLIT function with various clauses and filter conditions to split delimited list with single character seperator. If you like this post you may read through Tech-Recipes Database archive posts to learn some more useful stuff.