Ed Pollack deals with the lack of regular expressions in T-SQL:
This article is focused on how to write, simplify, and get the most out of string data using the least amount of code to do so. It’s a reminder that code complexity is equivalent to technical debt and that in these scenarios, less is always more.
Text-manipulating functions will be introduced and reviewed throughout this article, so feel free to perform searches against it to find what you are looking for.
Read on for mechanisms to split strings into multiple rows, build strings from multiple rows, and search+replace within strings.