Writing CLR table-valued functions with assemblies: introducing regular expressions into the database

The regular expression is very good, but it is not in the database, but it can be extended by way of an assembly

Write a DLL first, scalar functions well written, table-valued functions a little trouble

Here is the C # code

 using   System;  using   System.Data;  using   System.Data.SqlClient;  using   System.Data.SqlTypes;  using   Microsoft.SqlServer.Server;  using   System.Text.RegularExpressions;  using   System.Collections;  public  partial  class   regexpfunctions{ 

[SqlFunction (
DataAccess = Dataaccesskind.read,
FillRowMethodName = "Matchsfun_fillrow",
tabledefinition = "Pos Int,match NVARCHAR (500)")]
public static IEnumerable Matchsfun (string input, String patten)
{
MatchCollection MC;
Regex r = new Regex (patten);
MC = r.matches (input);
return MC;
}

public static void Matchsfun_fillrow (object mc,out int pos,out SqlString sqlmatch)
{
Match it = (match) MC;
pos = it. Index;
Sqlmatch = it. Value;
}

};

The assembly name is REGULEREXP2

There are a few explanations in the code:

(1) The table-valued function must be IEnumerable, simply speaking is the class that must have this interface, MatchCollection has this interface;

(2) A callback function must be provided, as indicated in the function property FillRowMethodName = "Matchsfun_fillrow", which is responsible for populating the data,

public static void Matchsfun_fillrow (object mc,out int pos,out SqlString sqlmatch)
{
Match it = (match) MC;
pos = it. Index;
Sqlmatch = it. Value;
}

What is the object MC here?

We can imagine the traversal

foreach (Match it in MC)
{
}

The Object MC Here is the match it in foreach.

The database then takes out int pos,out SqlString Sqlmatch The two quantities out to put into the table.

The next step is to add the assembly

The first step is to open the database of the CLR, do not elaborate, their own online search

Second step to add an assembly

The third step is to write a database table-valued function wrapper

Create FUNCTION [dbo].[matchlist](@input [nvarchar]( +),@patten [nvarchar]( +))RETURNS  TABLE(POSint,[Match] [nvarchar]( -)NULL)  with EXECUTE  asCALLER asEXTERNAL NAME[REGULEREXP2].[regexpfunctions].[Matchsfun]

OK.

Run

Select *  from [matchlist] ('abc','a|b')

Result is

If you are using a stored procedure

C # code is

[Microsoft.SqlServer.Server.SqlProcedure] Public Static voidMatches (stringInputstringPatten) {        //constructs a sqldatarecord like a table, where SqlMetaData is similar to DataColumnSqlDataRecord DataRecord =NewSqlDataRecord (Newsqlmetadata[] {NewSqlMetaData ("ID", SqlDbType.Int),NewSqlMetaData ("Index", SqlDbType.Int),NewSqlMetaData ("Match", SqlDbType.NVarChar, -)                    }); //Start fillingSqlContext.Pipe.SendResultsStart (DataRecord);        MatchCollection MC; Regex R=NewRegex (Patten); MC=r.matches (input);  for(inti =0; I < MC. Count; i++)        {                        //sqldatarecord.setstring function Similar to DataRow, filled with values like tableDatarecord.setint32 (0, i); Datarecord.setint32 (1, Mc[i].            Index); Datarecord.setstring (2, Mc[i].            Value); //data is filled to table by Sendresultsrow, related to TABLE.ROWS.ADD (DataRow);SqlContext.Pipe.SendResultsRow (DataRecord); }                //fill end, return result setSqlContext.Pipe.SendResultsEnd (); }

Write a stored procedure wrapper on the database side

CREATE PROCEDURE [dbo].[macths]    @input [nvarchar]( +),    @patten [nvarchar]( +) with EXECUTE  asCALLER asEXTERNAL NAME[Regulerexp].[Regulerexp].[Matches]

Anything else.

Run

exec [macths] ' ABC ','a|b'

Result is

Other scalar functions are simple, self-Baidu, similar