Using regular expressions in SQL Server

I. New. NET Class Library project

2. Create a class library project named Mssqlregexextend
4. Create a class named Regexextend
5. Copy the following code into the class[CSharp]View PlainCopy
   2. Using System.Text.RegularExpressions;
   6. Namespace Mssqlregexextend
   8. {
   12. public class Regexextend
   14. {
   16. // <summary>
   18. /// regular match
   20. // </summary>
   22. /// <param name= "regex" > Regular Expressions </param>
   24. /// <param name= "input" > Text </param>
   26. // <returns></returns>
   28. [Microsoft.SqlServer.Server.SqlFunction]
   30. public static string Match (string regex, string input)
   32. {
   34. return string. IsNullOrEmpty (input)? " ": new Regex (Regex, regexoptions.ignorecase). Match (input).  Value;
   36. }
   40. // <summary>
   42. /// regular replacement
   44. // </summary>
   46. /// <param name= "regex" > Regular Expressions </param>
   48. /// <param name= "input" > Text </param>
   50. /// <param name= "Replace" > Target to replace </param>
   52. // <returns></returns>
   54. [Microsoft.SqlServer.Server.SqlFunction]
   56. public static string replace (string regex, string input, string replace)
   58. {
   60. return string. IsNullOrEmpty (input)? " ": new Regex (Regex, regexoptions.ignorecase).  Replace (input, replace);
   62. }
   66. // <summary>
   68. /// regular check
   70. // </summary>
   72. /// <param name= "regex" > Regular Expressions </param>
   74. /// <param name= "input" > Text </param>
   76. // <returns></returns>
   78. [Microsoft.SqlServer.Server.SqlFunction]
   80. public static bool IsMatch (string regex, string input)
   82. {
   84. return! string. IsNullOrEmpty (input) && new Regex (Regex, regexoptions.ignorecase).  IsMatch (input);
   86. }
   88. }
   90. }
7. Right-click the project Build

Second, register the class library in MSSQL

Execute the following script in the database (the Class inventory drop address has to be modified correctly).

[SQL]View PlainCopy

2. --drop ASSEMBLY Regex
4. CREATE ASSEMBLY Regex from ' E:\CSharp\MSSQLRegexExtend\MSSQLRegexExtend\bin\Release\MSSQLRegexExtend.dll ' with Permission_set = SAFE --Register the. NET class Library
8. sp_configure ' clr enabled ', 1 --Set the database to use CLR components
10. RECONFIGURE --Set the available CLR components. Don't forget to run this line to apply
14. /**** The following code registers a static method in the class library as a function ****/
18. /**** regular Match ****/
20. --drop FUNCTION [dbo]. [Regex.match]
22. CREATE FUNCTION [dbo].[ Regex.match] (@Regex [nvarchar] (max), @Input [nvarchar] (max))
24. RETURNS [nvarchar] (max) with EXECUTE as CALLER
26. As
28. EXTERNAL NAME [regex].[ Mssqlregexextend.regexextend]. [Match]
32. /**** Regular Replacement ****/
34. --drop FUNCTION [dbo]. [Regex.Replace]
36. CREATE FUNCTION [dbo].[ Regex. Replace] (@Regex [nvarchar] (max), @Input [nvarchar] (max), @Replace [nvarchar] (max))
38. RETURNS [nvarchar] (max) with EXECUTE as CALLER
40. As
42. EXTERNAL NAME [regex].[ Mssqlregexextend.regexextend]. [Replace]
46. /**** Regular Check ****/
48. --drop FUNCTION [dbo]. [Regex.IsMatch]
50. CREATE FUNCTION [dbo].[ Regex.IsMatch] (@Regex [nvarchar] (max), @Input [nvarchar] (max))
52. RETURNS [Bit] with EXECUTE as CALLER
54. As
56. EXTERNAL NAME [regex].[ Mssqlregexextend.regexextend]. [IsMatch]

Iii. Invocation of the example

[SQL]View PlainCopy

2. SELECT [CustomerID]
4. , [CompanyName]
6. , [ContactName]
8. , [ContactTitle]
10. , [City]
12. , [Region]
14. , [PostalCode]
16. , [country]
18. , [Phone]
20. , [Fax]
22. , [Address]
24. , [dbo]. [Regex.match] (' (\d) + ', [Address]) as [house number] --Regular match
26. , [dbo]. [Regex. Replace] (' \d ', [Address],' * ') as [code the house number] --Regular replacement
28. From [Northwind]. [dbo]. [Customers]
30. where [dbo]. [Regex.IsMatch] (' \d ', [Address]) =1 --Check the record of the house number

Using regular expressions in SQL Server