Using a custom function in SQL

Because a table field in a database contains more HTML tags, you now need to modify the fields of the database to replace the HTML tags. Of course I can write a program to modify, after all, a bit of trouble. Executes directly in Query Analyzer, but MS SQL Server does not provide regular expressions, and replacing HTML tags is not easy. We can implement a custom function that replaces HTML tags with regular expressions through CLR hosting. (Note: SQL CLR can only be used in MS SQL Server 2005 or later)

First step: Create a new SQL Server project from Visual Studio 2008

Step Two: Configure the connected Database

After clicking on the previous step, the following prompt box appears

You can add a database connection by adding a new reference (only for MS SQL Server 2005 or later, remind again), and then click OK to select one of the databases later.

Step three: Add a custom function

Then you write the class of the custom function (Note that the method of the class must be static)

Using System;

Using System.Data;

Using System.Data.SqlClient;

Using System.Data.SqlTypes;

Using Microsoft.SqlServer.Server;

Using System.Text.RegularExpressions;

public partial class Userdefinedfunctions

{

public static readonly RegexOptions Options = Regexoptions.ignorepatternwhitespace | Regexoptions.singleline;

[Microsoft.SqlServer.Server.SqlFunction]

public static SqlString Regexreplace (SqlChars input, SqlString pattern, SqlString replace)

{

Regex regex = new Regex (pattern. Value, Options);

return regex. Replace (The new string (input. Value), replace. Value);

}

};

Fourth step: Deployment, deployment after class writing is complete

After deployment, a custom function and an assembly are generated below the connection database you specify ()

You can then invoke the custom function, but before invoking the custom function, you have to enable some configuration

EXEC sp_configure ' allow updates ', 0

EXEC sp_configure ' show advanced options ', 1--Make them available

Reconfigure

EXEC sp_configure ' clr enabled ', 1--Turn on OLE

Reconfigure

With these configurations enabled, you can invoke functions that have just been customized.

Call

SELECT dbo. Regexreplace (' <p>aaa</p> ', ' < (. [ ^>]*) > ', ')

The result: AAA

Using a custom function in SQL