Using regular expressions in SQL Server

Original address: http://www.cnblogs.com/colder/p/3796864.html

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" > Input string </param>//<param name= "pattern "> Regular Expressions </param>//<param name=" IgnoreCase "> Case-insensitive </param>///&LT;RETURNS&GT;&LT;/RETURNS&G    T    [Microsoft.SqlServer.Server.SqlFunction] public static bool Regexmatch (string input, string pattern, bool ignoreCase)        {bool IsMatch = 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); else match = Regex.match (input, pattern, Regexoptions.multiline |                regexoptions.compiled); if (match.            Success) IsMatch = true;    } catch {}} return isMatch; }//Get regular expression groupingsCharacters in///</summary>//<param name= "Input" > Input string </param>//<param name= "pattern" > Regular expression </param>//<param name= "GroupId" > Location of Group </param>//<param name= "Maxreturnlength" > returns the maximum length of characters  Degrees </param>///<returns></returns> [Microsoft.SqlServer.Server.SqlFunction] public static string Getregexmatchgroups (string input, string pattern, int groupId, int maxreturnlength) {string strreturn = string .        Empty; if (!string. IsNullOrEmpty (input) &&!string. IsNullOrEmpty (pattern)) {try {match match = Regex.match (input, pattern, rege Xoptions.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;    }} return strreturn; }

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.

Using regular expressions in SQL Server