This topic comes up a lot. At least daily there's a new question that either explicitly or implicitly needs a solution requiring some kind of string splitter. As SQL Server has evolved, new functions like STRING_SPLIT (SQL Server 2016) and STRING_AGG (SQL Server 2017) have made these tasks a lot easier, which unfortunately makes older answers and blog posts less relevant and certainly less optimal. Here are some posts where I address specific solutions and overall strategy (including trying to avoid splitting strings inside SQL Server at all, even with the new function, if you can).
- Split strings the right way – or the next best way A Follow-Up
- Performance Surprises and Assumptions : STRING_SPLIT() Follow-Up #1 Follow-Up #2
- Trusting STRING_SPLIT() order in Azure SQL Database
- Ordered String Splitting in SQL Server with OPENJSON
- Handling an unknown number of parameters with OPENJSON()
- SQL Server Split String Replacement Code with STRING_SPLIT
- Comparing string splitting / concatenation methods
- Solve old problems with SQL Server’s new STRING_AGG and STRING_SPLIT functions
- Dealing with the single-character delimiter in SQL Server's STRING_SPLIT function
- Another limitation of STRING_SPLIT
- Please help with STRING_SPLIT improvements
- A way to improve STRING_SPLIT in SQL Server – and you can help
- Splitting Strings : Now with less T-SQL