At last! Supported, official regular expression support in SQL Server! It’s a bit of a hack, so I may have to redact the “supported” comment. But here are the fully-documented scripts you get when you install Master Data Services on SQL 2008 R2.
Here’s how to extract them and use them on your own database (only tested on R2, but I suspect any edition of SQL server that supports the CLR would work with these; you will need R2 to install MDS, however):
- Install MDS from D:\MasterDataServices\x64\1033_ENU
- Run the Configuration Manager and set up a new MDS database. You will be copying from here and into your own database.
- Script the Microsoft.MasterDataServices.DataQuality assembly (right click / Script as / Create)
- Change the AUTHORIZATION to db_datareader
- Find the functions you need (most under scalar, some under table), and right click, script, create:
- Delete the USE block, and change the namespace to dbo, or one of your own.
- Run all of the scripts on your own database, starting with the assembly.