Use RegEx in SQL with CLR Procs

Wouldn't it be handy if you could use regular expressions (RegEx) in your SQL code? You could do some really advanced queries through the ability to specify regular expressions rather than just the simple like % matching you normally use.

SQL CLR Can Do That!

Yes, you can use the .NET regex library using a SQL CLR method registered as a SqlFunction. This example uses VistaDB 4, but the same code will work with SQL Server 2005 / 2008, by changing the using namespaces. I will demonstrate the same code in SQL Server in a later blog post.

Example SQL Query 

Here is what the query will look like when we are done. This is selecting all the orders where the ShipPostalCode is exactly 5 digits only (no letters, no spaces, etc.).

SELECT * FROM ORDERS WHERE LIKEREGEX
                 ( ShipPostalCode,  '^[0-9][0-9][0-9][0-9][0-9]$') = 1

CLR Method

Here is the complete C# method I will be using for the SQL REGEX. It is very simple, but that is part of the power of .NET. Imagine trying to write a regex parser using only SQL... No thanks!

[SqlFunction]
public static bool RegExMatch(string inputValue, string regexPattern )
{
    // Any nulls - we can't match, return false
    if (string.IsNullOrEmpty(inputValue) || string.IsNullOrEmpty(regexPattern))
        return false;
    
    Regex r1 = new Regex(regexPattern.TrimEnd(null));
    return r1.Match(inputValue.TrimEnd(null)).Success;
}

SQL to Load the Assembly into the Database

The following SQL code can be run in Data Builder and used to register the assembly and function with the name we want. See the actual vsql4 file in the sample project for nice error handling, and how to update the assembly if it already exists in the database.

CREATE ASSEMBLY [RegExCLR] FROM 'RegExCLR.dll';

-- Add the REGEX function.  We want a friendly name 
-- LIKEREGEX rather than the full namespace name.
-- Note the way we have to specify the Assembly.Namespace.Class.Function
-- NOTE the RegExCLR.RegExCLR 
-- (one is the assembly the other is the namespace)
CREATE FUNCTION LIKEREGEX ( @inputCalue NVARCHAR(4000), 
    @regexPattern NVARCHAR(4000) ) RETURNS BIT
    AS EXTERNAL NAME RegExCLR.RegExCLR.ClrClass.RegExMatch;

Now that we have it registered, we can call it. Take a look at the Example Calling script for examples of how to call the routine.

CLR Proc in Under 45 Minutes

This entire sample took less than 45 minutes to build. I hope it inspires you to look for more ways to use the CLR within your databases. The entire code can be found on the VistaDB Public Downloads site.

Watch the YouTube Video

I recorded a video of me walking through the steps above and demonstrating how the code works in VistaDB.  You can also go directly to YouTube to watch the CLR Proc example using Regex in HD.