Called in SQL Server 2008. Net,dll

Source: Internet
Author: User

    1. 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.
    2. 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

Related Article

Contact Us

The content source of this page is from Internet, which doesn't represent Alibaba Cloud's opinion; products and services mentioned on that page don't have any relationship with Alibaba Cloud. If the content of the page makes you feel confusing, please write us an email, we will handle the problem within 5 days after receiving your email.

If you find any instances of plagiarism from the community, please send an email to: info-contact@alibabacloud.com and provide relevant evidence. A staff member will contact you within 5 working days.

A Free Trial That Lets You Build Big!

Start building with 50+ products and up to 12 months usage for Elastic Compute Service

  • Sales Support

    1 on 1 presale consultation

  • After-Sales Support

    24/7 Technical Support 6 Free Tickets per Quarter Faster Response

  • Alibaba Cloud offers highly flexible support services tailored to meet your exact needs.