Koen Verbeeck makes use of regular expressions:
I have text stored in my SQL Server database, and I want to split it into all its different words. T-SQL has supported the STRING_SPLIT function since SQL Server 2016, but it only allows one single delimiter. There are multiple possible delimiters, such as commas, spaces, carriage returns and so on. It seems quite cumbersome to nest multiple STRING_SPLIT using APPLY. Is there a better option out there that doesn’t involve custom coding with CLR?
Read on to learn more. My understanding is that this method is similar in terms of performance to the other mechanisms we have available, like STRING_SPLIT(), tally tables, the APPLY operator, and CLR functions.