Deploy SQL 2008 R2 MDS Functions without MDS

Question

SQL Server 2008 R2 has a new Master Data Services feature that includes some really nice regular expression matching & extraction functions:

http://msdn.microsoft.com/en-us/library/ee633712.aspx

I am running 2008 R2, and want to run those functions outside of the MDS database.

How can I? Is there a way to extract them and reapply them to my own db?

Answer 1

You can access the .NET Regex classes via CLR integration in SQL 2005/2008. See: Regular Expressions Make Pattern Matching And Data Extraction Easier

Answer 2

I figured it out and fully documented it (with screenshots) on my blog. http://www.pettijohn.com/2010/08/regular-expressions-in-sql-2008-r2.html

In summary:

• 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 (Programmability / Assemblies / Right click / Script as / Create)
• Change the AUTHORIZATION to db_datareader
• Find the functions you need (regex are under scalar), 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.

Answer 3

Go grab it while it's hot.

Adapted string functions and assembly from Sql Server 2008 R2 MDS

The package includes the following functions:

TVFs:

• NGrams
• RegexMatches
• RegexSplit
• Split

SVFs:

• RegexExtract
• RegexIsMatch
• RegexIsValid
• RegexMask
• RegexReplace
• Similarity
• SimilarityDate
• XmlTransform

I altered the default schema to dbo.