Write clr Table value functions using an assembly: introduce regular expressions into the database

Regular Expressions are very good, but they are not in the database, but you can write a dll by means of Assembly extension. The scalar function is very easy to write. The following is a bit of trouble for table value functions: C # code usingSystem; usingSystem. data; usingSystem. data. sqlClient; usingSystem. data. sqlTypes; usingMicrosoft. sqlServ

Regular Expressions are very good, but they are not in the database. However, you can write a dll by means of Assembly extension. scalar functions are very easy to write, the following is the C # code using System; using System. data; using System. data. sqlClient; using System. data. sqlTypes; using Microsoft. sqlServ

Regular Expressions are very good, but they are not in the database, but can be extended through the assembly method.

Compile a dll first. The scalar function is very easy to write, and the table value function is a little more troublesome.

Below 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 several explanations in the Code:

(1) The table-valued function must be IEnumerable. In short, it must be a class with this interface, and MatchCollection has this interface;

(2) A callback function must be provided, which is specified in FillRowMethodName = "MatchsFun_FillRow" of the function attribute. This function is used to fill 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 object mc here?

We can imagine traversing

Foreach (Match it in mc)
{
}

The object mc here is the Match it in foreach.

Then the database extracts the out int pos and out SqlString sqlmatch values and puts them into the table.

The next step is to add an assembly.

The first step is to open the database clr. If you do not need to elaborate, check it online.

Step 2 add an assembly

Step 3: Write a database table value function package

create FUNCTION [dbo].[MatchList](@input [nvarchar](1000), @patten [nvarchar](1000))RETURNS  TABLE (    pos int,[match] [nvarchar](500) NULL) WITH EXECUTE AS CALLERAS EXTERNAL NAME [RegulerExp2].[RegExpFunctions].[MatchsFun]

Yes.

I wrote a small example above. It seems that you are not interested. Let's write a practical example. There is no split function in the database, so it is easy to use regular expressions.

select match from dbo.MatchList('1,2,4,12,24,41','(?<=,|^).*?(?=,|$)')

Result:

If you use a stored procedure

C # The code is

[Microsoft. sqlServer. server. sqlProcedure] public static void Matches (string input, string patten) {// construct SqlDataRecord like a Table, sqlMetaData is similar to DataColumn SqlDataRecord dataRecord = new SqlDataRecord (new SqlMetaData [] {new SqlMetaData ("ID", SqlDbType. int), new SqlMetaData ("index", SqlDbType. int), new SqlMetaData ("match", SqlDbType. NVarChar, 100)}); // starts filling SqlContext. pipe. sendResultsStart (dataRecord); MatchCollection mc; Regex r = new Regex (patten); mc = r. matches (input); for (int I = 0; I <mc. count; I ++) {// SqlDataRecord. setString is similar to the DataRow function, such as filling in dataRecord in Table. setInt32 (0, I); dataRecord. setInt32 (1, mc [I]. index); dataRecord. setString (2, mc [I]. value); // use SendResultsRow to fill the data in the Table, which is related to the Table. rows. add (DataRow); SqlContext. pipe. sendResultsRow (dataRecord);} // the completion of filling. SqlContext is returned. pipe. sendResultsEnd ();}

Write a stored procedure package on the Database End

CREATE PROCEDURE [dbo].[Macths]    @input [nvarchar](1000),    @patten [nvarchar](1000)WITH EXECUTE AS CALLERASEXTERNAL NAME [RegulerExp].[RegulerExp].[Matches]

Same as others

Run

exec dbo.Macths '1,2,4,12,24,41','(?<=,|^).*?(?=,|$)'

Result:

Other scalar functions are very simple, like Baidu.