Create a faster regular expression clr function

Question

I am trying to improve the Clr function in the link http://msdn.microsoft.com/en-us/magazine/cc163473.aspx .

public static partial class UserDefinedFunctions 
{
    public static readonly RegexOptions Options =
        RegexOptions.IgnorePatternWhitespace |
        RegexOptions.Singleline;

    [SqlFunction]
    public static SqlBoolean RegexMatch(
        SqlChars input, SqlString pattern)
    {
        Regex regex = new Regex( pattern.Value, Options );
        return regex.IsMatch( new string( input.Value ) );
    }
}

When execute select * from Table1 where dbo.RegexMatch(col1, 'pattern') = 1, the Clr function create a new Regex object for each row in the table.

Is it possible to create only one Regex object for each Sql statement? For each row just call regex.Ismatch(...). Is the following code valid?

public static partial class UserDefinedFunctions 
{
    public static readonly RegexOptions Options =
        RegexOptions.IgnorePatternWhitespace |
        RegexOptions.Singleline;

    static Regex regex = null;

    [SqlFunction]
    public static SqlBoolean RegexMatch(
        SqlChars input, SqlString pattern)
    {
        if (regex == null) 
            regex = new Regex( pattern.Value, Options );
        return regex.IsMatch( new string( input.Value ) );
    }
}

Answer 1

Your UDF based instance of your static regex is probably not even re-used, Your better of calling the static version of

System.Text.RegularExpressions.RegEx.IsMatch(string input, string pattern, RegexOptions options);

directly.

Please note that in debug mode things work different than that what they do in production and that SQL is going to free memory when it needs to.

Also, try using RegexOptions.Compiled and CultureInvariant.