用程序集编写clr表值函数：把正则表达式引入数据库中

正则表达式非常好，但在数据库中就是没有，但可以通过程序集方式扩展 先编写一个dll，标量函数很好写，表值函数麻烦一点 下面是C#代码 using System; using System.Data; using System.Data.SqlClient; using System.Data.SqlTypes; using Microsoft.SqlServ

正则表达式非常好，但在数据库中就是没有，但可以通过程序集方式扩展

先编写一个dll，标量函数很好写，表值函数麻烦一点

下面是C#代码

<span>using</span><span> System;
</span><span>using</span><span> System.Data;
</span><span>using</span><span> System.Data.SqlClient;
</span><span>using</span><span> System.Data.SqlTypes;
</span><span>using</span><span> Microsoft.SqlServer.Server;
</span><span>using</span><span> System.Text.RegularExpressions;
</span><span>using</span><span> System.Collections;
</span><span>public</span> <span>partial</span> <span>class</span><span> RegExpFunctions
{</span>

　　　　[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;
　　　　}

};

程序集名称是RegulerExp2

代码中有几点解释一下：

（1）表值函数必须是IEnumerable，简单讲是必须有这个接口的类，MatchCollection就具有这个接口；

（2）必须提供一个回调函数，在函数属性FillRowMethodName = "MatchsFun_FillRow"中指明，这个函数负责填充数据，

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

这里的object mc是什么呢？

我们可以想象一下遍历

foreach (Match it in mc)
{　　
}

这里的object mc就是foreach里的Match it。

然后数据库把out int pos,out SqlString sqlmatch这两个量取出去放进表里。

 

下一步是添加程序集

第一步是把数据库的clr打开，不细说，自己网上查

第二步添加程序集

第三步，写一个数据库表值函数包装

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

 

可以了。

前面写了一个小示例，大家好像没兴趣，写一个实用点的例子吧。数据库中没有split函数，用正则表达式就很容易了

<span>select</span> match <span>from</span> dbo.MatchList(<span>'</span><span>1,2,4,12,24,41</span><span>'</span>,<span>'</span><span>(?<span>'</span>)</span>

 

结果为

 

如果以一个存储过程方式

C#代码为

<span>[Microsoft.SqlServer.Server.SqlProcedure]
    </span><span>public</span> <span>static</span> <span>void</span> Matches(<span>string</span> input, <span>string</span><span> patten)
    {
        </span><span>//</span><span>像构造Table一样来构造SqlDataRecord，其中SqlMetaData类似DataColumn</span>
        SqlDataRecord dataRecord = <span>new</span> SqlDataRecord(<span>new</span><span> SqlMetaData[] {                
                </span><span>new</span> SqlMetaData(<span>"</span><span>ID</span><span>"</span><span>, SqlDbType.Int),
                </span><span>new</span> SqlMetaData(<span>"</span><span>index</span><span>"</span><span>, SqlDbType.Int),
                </span><span>new</span> SqlMetaData(<span>"</span><span>match</span><span>"</span>, SqlDbType.NVarChar,<span>100</span><span>)
                    });
        </span><span>//</span><span>开始填充</span>
<span>        SqlContext.Pipe.SendResultsStart(dataRecord);

        MatchCollection mc;
        Regex r </span>= <span>new</span><span> Regex(patten);
        mc </span>=<span> r.Matches(input);
        </span><span>for</span> (<span>int</span> i = <span>0</span>; i )
        {            
            <span>//</span><span>SqlDataRecord.SetString类似DataRow的功能，像Table中填充值</span>
            dataRecord.SetInt32(<span>0</span><span>, i);
            dataRecord.SetInt32(</span><span>1</span><span>, mc[i].Index);
            dataRecord.SetString(</span><span>2</span><span>,  mc[i].Value);
            </span><span>//</span><span>通过SendResultsRow把数据填充到Table，相关于Table.Rows.Add(DataRow);</span>
<span>            SqlContext.Pipe.SendResultsRow(dataRecord);
        }

        
        </span><span>//</span><span>填充结束，返回结果集</span>
<span>        SqlContext.Pipe.SendResultsEnd();
    }</span>

数据库端写一个存储过程包装

<span>CREATE</span> <span>PROCEDURE</span> <span>[</span><span>dbo</span><span>]</span>.<span>[</span><span>Macths</span><span>]</span>
    <span>@input</span> <span>[</span><span>nvarchar</span><span>]</span>(<span>1000</span><span>),
    </span><span>@patten</span> <span>[</span><span>nvarchar</span><span>]</span>(<span>1000</span><span>)
</span><span>WITH</span> <span>EXECUTE</span> <span>AS</span><span> CALLER
</span><span>AS</span><span>
EXTERNAL NAME </span><span>[</span><span>RegulerExp</span><span>]</span>.<span>[</span><span>RegulerExp</span><span>]</span>.<span>[</span><span>Matches</span><span>]</span>

别的一样

运行

<span>exec</span> dbo.Macths <span>'</span><span>1,2,4,12,24,41</span><span>'</span>,<span>'</span><span>(?<span>'</span></span>

 

结果为

 

其他标量函数很简单，自己百度，类似