- Create a new class library program named Regex, open Visual Studio 2008, click File, click New, click Project, select the class library in the New Project dialog box that pops up, and the project name is regex.
- Name the class Class1 in your project as regex, and write the following code in this class:
Using System;
Using System.Collections.Generic;
Using System.Linq;
Using System.Text;
Using System.Data.SqlTypes;
Using System.Text.RegularExpressions;
Using Microsoft.SqlServer.Server;
Namespace RegExp
{
public partial class RegExp
{
[SqlFunction (IsDeterministic = true, DataAccess = Dataaccesskind.none)]
public static SqlString Regexreplace (SqlString input, SqlString pattern, SqlString replacement)
{
Return (SqlString) regex.replace (input. Value, pattern. Value, replacement. Value);
}
}
}This class uses the Replace function in the System.Text.RegularExpressions.Regex class, which is a common function in C # and uses regular expressions to implement the Replace function. Compiling this class library project generates RegExp.dll, which will be used later.
- The following opens the SQL Server 2008 management interface, we need to deploy this DLL to the database, and then register a method, but before that, we need to open the CLR invocation function in SQL Server, run the following SQL statement:
exec sp_configure ' clr enabled ', 1;
Reconfigure
- Run the following statement to extract the intermediate language (IL) from this DLL, and if you try it yourself, pay attention to modifying the DLL file storage path.
Use AdventureWorks;
Create assembly REGEXP from ' D:\MyProject\RegExp\RegExp\bin\Debug\RegExp.dll ' This time we can view this collection in SQL Server, Click Expand Database AdventureWorks, click Expand Programmability, click Expand Assemblies to see the regex, such as 1.
Figure 1
- Write a SQL function to register this assembly, the code is as follows
Create FUNCTION dbo. Regexreplace (
@input as nvarchar (max),
@pattern as nvarchar (max),
@replacement as nvarchar (max)
Returns nvarchar (max)
With returns NULL on NULL input
external [REGEXP]. [Regexp.regexp]. [Regexreplace]
Go Note:
A. The function is returns rather than return and is easily mistaken.
B.with returns NULL on NULL input means that if any of the arguments are null when the function is called, the return value of the function will be null.
C. The last sentence refers to the format of the class library, which I originally thought [Myassemblyname]. [Myassemblyname]. [Mymethodname] is OK, but it needs to be written [Myassemblyname]. [Myassemblyname.myclassname]. [Mymethodname] This can only run the above statement, or the error is not found in the assembly related classes, so far puzzled, very confusing.
- Now we can call this function just like any other SQL function, and here are some examples of calls.
It's easy to replace the letter Z in China with Z.
This replaces the operator + with the Add.
This is the result of the statement with the RETURNS null on NULL input.
Called in SQL Server 2008. Net,dll
