T-SQL regular expressions on MS SQL 2005

One of the most anticipated features of SQL Server 2005 is the integration of the .Net CLR into the SQL Server engine. This article is going to show how to write .Net code and use it in SQL Server 2005. As an example, I am going to write a function for regular expression search. SQL Server 2005 hosts the .Net CLR and will load and execute DLLs, which can also be referred to as assemblies. Assemblies may be created in C# or VB.Net.  Before doing any .Net coding in SQL Server you must enable the CLR. Along with many other features in SQL Server 2005,  the CLR is “off by default”. This is done to limit security vulnerabilities. The first piece of code is the script that you’ll need to enable at the CLR. Here it is:

EXEC sp_configure 'show advanced options' , '1';
go

reconfigure;
go

EXEC sp_configure 'clr enabled' , '1'
go

reconfigure;
go

Now let’s write a User-Defined Function (UDF). The first step is to create a “SQL Server Project” in your preferred language, either C# or VB.Net. Second, add a User-Defined Function and Visual Studio used a template to create the framework of a UDF.

Here is an example of my FindByPattern function:

using System;
using System.Data;
using System.Data.SqlClient;
using System.Data.SqlTypes;
using Microsoft.SqlServer.Server;
using System.Text.RegularExpressions;

public partial class UserDefinedFunctions
{
    [Microsoft.SqlServer.Server.SqlFunction]
    public static SqlString FindByPattern(string input, string pattern)
    {

        Regex reg = new Regex(pattern, RegexOptions.IgnoreCase | RegexOptions.Multiline);
        Match match = reg.Match(input);

        if (match.Success)
        {
            string result = match.Groups[0].ToString();
            return new SqlString(result);
        }
        else
            return new SqlString("");
    }
}

Execute the project.

Then you can use this function in your T-SQL statements:

SELECT dbo.FindByPattern('Viktar is the best!','^.{6}')

Result:

Viktar