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 )
{
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';
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.