Monday, August 30, 2010

Regular Expressions in SQL 2008 R2

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)
    image
    image
  • Change the AUTHORIZATION to db_datareader
  • Find the functions you need (most under scalar, some under table), and right click, script, create:
    image
    image
  • Delete the USE block, and change the namespace to dbo, or one of your own.
    image
  • Run all of the scripts on your own database, starting with the assembly.

3 comments:

Matt Penner said...

Thanks for the great tip. However, I wonder what the legality is of using these functions on non-DQM licensed SQL servers. For instance, can we install these on a SQL Standard license?

Jason Simmons said...

Sir... this article is great if you have SQL Server Enterprise installed. I only have Standard and can't get the MDS Database created in the first place. It is saying that it isn't compatible with my version of SQL Server. Am I missing a step to get to the assemblies?

Travis said...

@Jason - I believe you need Enterprise, though it's been a few years since I've used this trick so can't be certain.