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';






EXEC sp_configure 'clr enabled' , '1'






 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



    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);



            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}')





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



Re:T-SQL regular expressions on MS SQL 2005

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


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

New Comment

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


Valid HTML5