Using regular expressions in SQL Server

SQL Server 2005 and later versions are supported in the CLR language (C #. NET, VB. NET) to write processes, triggers and functions, so that regular matching, data extraction can be used flexibly in SQL, greatly improve the SQL processing strings, text and other content flexibility and efficiency.

Operation Steps:

1. Create a new SQL Server project (enter user name, password, select db), and after new, you can change the

2. Create a new class "RegexMatch.cs" and select a user-defined function

As you can see, the class is a partial class: public partial class Userdefinedfunctions

You can now write methods in this class, noting that the properties of the method are: [Microsoft.SqlServer.Server.SqlFunction]

Now add the following two methods to the class:

///whether to match regular expressions/// </summary>    /// <param name= "Input" >the input string</param>    /// <param name= "pattern" >Regular Expressions</param>    /// <param name= "IgnoreCase" >whether to ignore uppercase and lowercase</param>    /// <returns></returns>[Microsoft.SqlServer.Server.SqlFunction] Public Static BOOLRegexmatch (stringInputstringPatternBOOLignoreCase) {        BOOLIsMatch =false; if(!string. IsNullOrEmpty (input) &&!string. IsNullOrEmpty (pattern)) {Try{match match=NULL; if(ignoreCase) match= Regex.match (input, pattern, Regexoptions.multiline | Regexoptions.ignorecase |regexoptions.compiled); ElseMatch= Regex.match (input, pattern, Regexoptions.multiline |regexoptions.compiled); if(match. Success) IsMatch=true; }            Catch { }        }        returnIsMatch; }    ///get the characters in a regular expression group/// </summary>    /// <param name= "Input" >the input string</param>    /// <param name= "pattern" >Regular Expressions</param>    /// <param name= "GroupId" >Location of grouping</param>    /// <param name= "Maxreturnlength" >returns the maximum length of a character</param>    /// <returns></returns>[Microsoft.SqlServer.Server.SqlFunction] Public Static stringGetregexmatchgroups (stringInputstringPatternintGroupId,intmaxreturnlength) {        stringStrreturn =string.        Empty; if(!string. IsNullOrEmpty (input) &&!string. IsNullOrEmpty (pattern)) {Try{match match= Regex.match (input, pattern, Regexoptions.multiline | Regexoptions.ignorecase |regexoptions.compiled); if(Match. Success && (GroupId <match. Groups.count)) {Strreturn=match. Groups[groupid].                    Value; Strreturn= (strreturn.length <= maxreturnlength)? StrReturn:strReturn.Substring (0, maxreturnlength); }            }            Catch            {                return string.            Empty; }        }        returnStrreturn; }

3. The next step is the issue of deployment, click on the project right--"deployment can

Tip deployment succeeds, you can have these two methods in a scalar-valued function of the database.

Use the same methods as normal SQL functions:

SELECT dbo. Regexmatch ('/book/103.aspx ', '/book/(\d+). aspx ', ' true ')
Msg 6263, Level 16, State 1, line 1th
Prohibit execution of user code in the. NET Framework. Enable the "CLR enabled" configuration option.

--This error occurs, under configuration:

exec sp_configure ' clr enabled ', 1;
Reconfigure with override;

Whether it matches:

SELECT dbo. Regexmatch ('/book/103.aspx ', '/book/(\d+). aspx ', 1)

Returns 1, indicating a successful match

SELECT dbo. Regexmatch ('/book/103.aspx ', '/book/(\d+). aspx ', 0)

Represents 0, matching fails (case not ignored).

Data extraction:

SELECT dbo. Getregexmatchgroups ('/book/103.aspx ', '/book/(\d+). aspx ', 1,50)

Returns 103, very convenient to extract.

Note: When using the CLR in SQL, try catch is used as much as possible ... Avoid exceptions.