Click here to Skip to main content
13,507,742 members
Click here to Skip to main content
Add your own
alternative version


24 bookmarked
Posted 5 Jun 2010

Use RegEx in SQL with CLR Procs

, 7 Aug 2017
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 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.).

                 ( 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!

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.


-- 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)
    @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



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


About the Author

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.

You may also be interested in...


Comments and Discussions

GeneralI use CLR functions (including RegEx) all the time... Pin
PIEBALDconsult7-Aug-17 13:04
protectorPIEBALDconsult7-Aug-17 13:04 
GeneralMy vote of 3 Pin
mbbblue15-Dec-14 6:17
membermbbblue15-Dec-14 6:17 
GeneralMy vote of 1 Pin
Nooner18-May-13 18:45
memberNooner18-May-13 18:45 
GeneralRe: My vote of 1 Pin
JasonShort7-Jun-13 11:19
memberJasonShort7-Jun-13 11:19 
GeneralIncrease perfomance Pin
Dmitry O. Kiselev14-Jun-10 20:15
memberDmitry O. Kiselev14-Jun-10 20:15 
GeneralRe: Increase perfomance Pin
JasonShort7-Jun-13 11:18
memberJasonShort7-Jun-13 11:18 
GeneralHere is the rest for you :) Pin
leppie13-Jun-10 20:24
memberleppie13-Jun-10 20:24 
GeneralLooks good! Pin
Sandeep Mewara5-Jun-10 21:31
mentorSandeep Mewara5-Jun-10 21:31 

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

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

Permalink | Advertise | Privacy | Terms of Use | Mobile
Web03 | 2.8.180417.1 | Last Updated 8 Aug 2017
Article Copyright 2010 by JasonShort
Everything else Copyright © CodeProject, 1999-2018
Layout: fixed | fluid