Click here to Skip to main content
Click here to Skip to main content
Technical Blog

Use RegEx in SQL with CLR Procs

, 7 Jun 2010 CPOL
Rate this:
Please Sign up or sign in to vote.
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!

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 Proc for REGEX in VistaDB 4

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

License

This article, along with any associated source code and files, is licensed under The Code Project Open License (CPOL)

Share

About the Author

JasonShort
Software Developer (Senior)
United States United States
I hold a PhD in computer science, and have been a practicing developer since the early 90's.
 
I used to be the owner for VistaDB, but sold the product to another company in August 2010.
 
I have recently moved to Redmond and now work for Microsoft. Any posts or articles are purely my own opinions, and not the opinions of my employer.
Follow on   Twitter

Comments and Discussions

 
GeneralMy vote of 3 Pinmembermbbblue15-Dec-14 7:17 
GeneralMy vote of 1 PinmemberNooner18-May-13 19:45 
GeneralRe: My vote of 1 PinmemberJasonShort7-Jun-13 12:19 
GeneralIncrease perfomance PinmemberDmitry O. Kiselev14-Jun-10 21:15 
GeneralRe: Increase perfomance PinmemberJasonShort7-Jun-13 12:18 
GeneralHere is the rest for you :) Pinmemberleppie13-Jun-10 21:24 
GeneralLooks good! PinmentorSandeep Mewara5-Jun-10 22:31 

General General    News News    Suggestion Suggestion    Question Question    Bug Bug    Answer Answer    Joke Joke    Rant Rant    Admin Admin   

Use Ctrl+Left/Right to switch messages, Ctrl+Up/Down to switch threads, Ctrl+Shift+Left/Right to switch pages.

| Advertise | Privacy | Terms of Use | Mobile
Web04 | 2.8.141216.1 | Last Updated 7 Jun 2010
Article Copyright 2010 by JasonShort
Everything else Copyright © CodeProject, 1999-2014
Layout: fixed | fluid