Deploy SQL 2008 R2 MDS Functions without MDS

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

  • How to join two tables
  • How can I skip items in a foreach loop container set to foreach ado enumerator?
  • Entity Framework: Generated SQL - cannot find data type datetime2
  • Generate view with X and Y from geometry type
  • sqlcmd: Login failed for user 'NT AUTHORITY\ANONYMOUS LOGON'
  • How to display progress bar while executing big SQLCommand VB.Net
  • 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?

  • SQL Server convert string to datetime
  • Can you subtotal rows and/or columns in a pivot table?
  • Return specific data from SQL query and error invalid column name
  • SQL Query to find the last day of the month
  • ms sql row_number() function - won't let me use within the same statement
  • SQL Server equivalent to DBMS_METADATA.GET_DDL
  • 3 Solutions collect form web for “Deploy SQL 2008 R2 MDS Functions without MDS”

    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.

    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.

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

    MS SQL Server is a Microsoft SQL Database product, include sql server standard, sql server management studio, sql server express and so on.