MS SQL RegEx Support

30. January 2011 14:47

Something that annoyed me slightly when moving from mysql to mssql was the complete lack of natural RegEx support. Here is some code that will enable you to use enable RegEx support in MS SQL. It will work with both 2005 and 2008.

 

public partial class UserDefinedFunctions
{
    [Microsoft.SqlServer.Server.SqlFunction(IsDeterministic = true, IsPrecise = true)]
    public static SqlBoolean RegEx(SqlString Value, SqlString Pattern)
    {
        if (Pattern.IsNull || Value.IsNull)
            return false;

        return Regex.IsMatch(Value.ToString(), Pattern.ToString());
    }
}

 

To install the function you will need to enable CLR support compile and install the assembly. Then you will need the following sql

 

CREATE Function RegEx(@Input nvarchar(512), @Pattern nvarchar(512))
	RETURNS BIT AS EXTERNAL NAME SqlExtensions.UserDefinedFunctions.RegEx
GO

You should be able to call the function in normal sql code like SELECT dbo.RegEx('value', '/vali/')

 

E-mail Kick it! DZone it! del.icio.us Permalink


Pingbacks and trackbacks (1)+