次の方法で共有

Working with Regular Expressions

  • [アーティクル]

One of the many benefits of using CLR Integration in your database applications is the availability of rich .NET Framework libraries. SQL Server provides you with a set of built-in functions that can be used in your queries but with CLR Integration you can enhance this set to any functionality from the list of supported framework libraries for e.g. System.dll.

One such very useful functionality that this enables is to match patterns using regular expressions. You can use System.Text.RegularExpressions to write a few lines of code that can do pattern matching. The following C# code can be used as a function in your queries (once it is deployed to your database) to perform pattern matching:

using System;
using Microsoft.SqlServer.Server;
using System.Text.RegularExpressions;

 

public partial class RegExBase
{
[SqlFunction(IsDeterministic = true, IsPrecise = true)]
public static bool RegExMatch(string pattern, string matchString)
{
Regex r1 = new Regex(pattern.TrimEnd(null));
return Regex.Match(matchString.TrimEnd(null)).Success;
}
};

You can now use it in your queries.

Following query returns rows from a table Person.Contact that contain valid email addresses:

select ContactID, FirstName, LastName, EmailAddress, Phone
from Person.Contact
where dbo.RegExMatch('[a-zA-Z0-9_\-]+@([a-zA-Z0-9_\-]+\.)+(com|org|edu|nz|au)', EmailAddress) = 1

Another good use of regular expression function is as a constraint in your table. Suppose you want to insert only those rows that have valid email addresses and phone numbers. You can do this as follows:

create table Contacts (
FirstName nvarchar(30),
LastName nvarchar(30),
EmailAddress nvarchar(30) CHECK (dbo.RegExMatch('[a-zA-Z0-9_\-]+@([a-zA-Z0-9_\-]+\.)+(com|org|edu|nz)', EmailAddress)=1),
USPhoneNo nvarchar(30) CHECK (dbo.RegExMatch('\([1-9][0-9][0-9]\) [0-9][0-9][0-9]\-[0-9][0-9][0-9][0-9]', UsPhoneNo)=1))

Now coming back to the code we wrote in our function. You might notice that we are creating an instance of the regular expression class for each invocation. In the above query, if your table has a million rows, this function would be called a million times and hence creating an instance a million times which could be expensive and hurt performance. SQL Server allows you to use readonly static variables in your code. We can thus change the above code to have the email address regular expression created in a readonly static as follows:

using System;
using Microsoft.SqlServer.Server;
using System.Text.RegularExpressions;

public partial class RegExStatic
{
private readonly static Regex r1 = new Regex("[a-zA-Z0-9_\\-]+@([a-zA-Z0-9_\\-]+\\.)+(com|org|edu|nz|au)");

[SqlFunction(IsDeterministic = true, IsPrecise = true)]
public static bool validateEmailStatic(string matchString)
{
return r1.Match(matchString.TrimEnd(null)).Success;
}
};

This can be used in your query as follows:

select * from Person.Contact where
dbo.validateEmailStatic(EmailAddress) = 1

The above code would perform better for large data since it would cache the instance created during the first invocation and use it in future. However, as you can notice, this requires you to know your pattern at the time of writing your code. Many times this might not be an option or you might not want to write one method for every regular expression that you want to use. Regular expression library has an option that does this kind of caching for you. You can use the RegexOptions.Compiled option to enable this caching. This option would compile the regular expression once and use in future invocations for the same regular expression. However, this caching is currently available only in the static Match method on Regex. Thus, this allows you to have the flexibility of passing arbitrary patterns to your method and still not loose performance.

Following is the code to do this:

using System;
using Microsoft.SqlServer.Server;
using System.Text.RegularExpressions;

public partial class RegExCompiled
{
[SqlFunction(IsDeterministic = true, IsPrecise = true)]
public static bool RegExCompiledMatch(string pattern, string matchString)
{
return Regex.Match(matchString.TrimEnd(null), pattern.TrimEnd(null), RegexOptions.Compiled).Success;
}
};

There is another option where you can pre compile your pattern into an assembly and then use it in your methods. This could be useful for complex patterns that take a lot of time to compile. Following code shows you how to compile your expression into an assembly:

using System;
using System.Reflection;
using System.Text.RegularExpressions;

public class RegexAsm {
public static void Main()
{
RegexCompilationInfo rci = new RegexCompilationInfo("[a-zA-Z0-9_\\-]+@([a-zA-Z0-9_\\-]+\\.)+(com|org|edu|nz|au)", RegexOptions.None, "EmailRegex", "", true);
AssemblyName an = new AssemblyName();
an.Name = "EmailRegex";
Regex.CompileToAssembly(new RegexCompilationInfo[] { rci }, an);
}
}

Now you can define a SQL CLR function that uses the assembly created by the above code as follows:

 

using System;
using Microsoft.SqlServer.Server;
using System.Text.RegularExpressions;

public partial class RegExAssembly
{
[SqlFunction(IsDeterministic = true, IsPrecise = true)]
public static bool validateEmailCompiled(string matchString)
{
EmailRegex emailRegex = new EmailRegex();
return emailRegex.Match(matchString.TrimEnd(null)).Success;
}
};

Today we saw how a few lines of CLR code can enable a great functionality in your queries and let you do things that were very difficult or impossible in the past. Continue to watch this space for more useful information coming in soon.

- Vineet Rao

Microsoft SQL Server

This posting is provided "AS IS" with no warranties, and confers no rights.

Comments

  • Anonymous
    July 04, 2005

    Here is an oldie but a goodie: Jason Antonelli describes the URL Rewriting strategies used for MSN...
  • Anonymous
    September 02, 2005
    The comment has been removed
  • Anonymous
    September 09, 2005
    I found this a particularly useful example.
  • Anonymous
    December 05, 2005
    The comment has been removed
  • Anonymous
    December 08, 2005
    Great examples and exactly what I've been looking for. I have a question regarding whether resource usage would become a problem when using RegExOptions.Compiled flag and passing a different matchString each time.

    MSDN says the following about compiled regular expressions.

    "However, generated MSIL cannot be unloaded. The only way to unload code is to unload an entire application domain (that is, to unload all of your application's code.). Effectively, once a regular expression is compiled with the RegexOptions.Compiled option, the .NET Framework never releases the resources used by the compiled expression, even if the Regex object itself is released to garbage collection."
  • Anonymous
    May 26, 2006
    I have been thinking of moving our regular expression matching process to SQL server and I found this article very useful on that.
  • Anonymous
    August 01, 2006
    Just trying to get this going but I don't know mcuh about CLR or UDF's.  Any advice on how I can get a CLR to work that allows me to search a table of regular expressions and find the record that matches the dynamically provided input value?

    Thanks!
  • Anonymous
    March 26, 2008
    As it stands today complex expression matching within SQL Server 2005 is "possible" at best and a nightmare