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 maybe 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 in an effort to limit security vulnerabilities. Out 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]

    publicstaticSqlString 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

 


Posted on Tuesday, April 29, 2008 by | Comments (1) | Add Comment

Comments

Gravatar

Re:T-SQL regular expressions on MS SQL 2005

Great function Viktar, works as a split function in sql.

Thanks,
Sudhakar

Posted on 7/8/2009 7:42:12 AM by Sudhakar #

New Comment

Your Name:
Email (for internal use only):
Comment:
 
Code above:

Categories

Recent Tweets

  • Simon Ince's Blog: Hierarchies with HierarchyID in SQL 2008 http://t.co/xSDwiF6rRS.
  • Visual Studio 2010 WAS painfully slow - CodeProject http://t.co/Usba1x6CZy

Valid HTML5