正则表达式第六篇：使用 C# 正则表达式实现 CLR 标量函数

编写C#代码，实现CLR标量函数，在SQL Server中执行复杂的正则查询和匹配。

一，Regex类

Regex类用于表示一个正则表达式，执行匹配、替换和拆分操作，Regex类有五大方法：

IsMatch()：是否匹配到正则
Match()：返回正则的第一个匹配
Matches()：返回正则的全部匹配
Replace()：把匹配正则表达式的文本替换掉
Split()：把输入文本拆分，拆分的边界是匹配正则表达式的文本

1，创建Regex 对象

创建Regex对象，并指定正则选项（忽略大小写）：

Regex re = new Regex("(?<=\"UserID\":\").*?(?=\")", RegexOptions.IgnoreCase);
string mat = re.Match(input_text).Value;

也可以直接使用静态方法，直接获取到第一个匹配的值：

string mat = Regex.Match(input_txt,"(?<=\"UserID\":\").*?(?=\")", RegexOptions.IgnoreCase).Value;

2，查找匹配

按照正则来查看匹配的文本是正则表达式最常用的功能，

Regex re = new Regex("(?<=\"UserID\":\").*?(?=\")", RegexOptions.IgnoreCase);

MatchCollection mc = re.Matches(text_input);
foreach(Match mt in mc)
{
    //mt.Value
}

二，创建CLR工程

我使用的是VS2017 Enterprise版。

1，创建SQL Server Project

2，新建CLR函数

在已创建的SQL Server Project中添加新项目（Add -> New Item)，把文件命名为UserDefinedFunctions.cs

3，编写CLR代码

完整的CLR标量函数示例代码如下，Build 该文件，生成DLL文件，用该DLL文件创建程序集。

为了使用正则表达式，需要在文件中添加引用： using System.Text.RegularExpressions;

using System;
using System.Data;
using System.Data.SqlClient;
using System.Data.SqlTypes;
using Microsoft.SqlServer.Server;
using System.Text;
using System.Text.RegularExpressions;

public partial class UserDefinedFunctions
{
    [Microsoft.SqlServer.Server.SqlFunction]
    public static SqlString Match(string input, string pattern)
    {
        string str = Regex.Match(input, pattern, RegexOptions.IgnoreCase).Value;
        return new SqlString (str);
    }

    public static SqlBoolean IsMatch(string input, string pattern)
    {
        bool match = Regex.IsMatch(input, pattern, RegexOptions.IgnoreCase);
        return new SqlBoolean(match);
    }

    public static SqlString Matches(string input, string pattern)
    {
        MatchCollection mc = Regex.Matches(input, pattern, RegexOptions.IgnoreCase);
        StringBuilder strList = new StringBuilder();
        int idx = 0;
        foreach(Match m in mc)
        {
            strList.Append(string.Format("\"idx{0}\":\"{1}\",", idx, m.Value));
            idx = idx + 1;
        }
        return new SqlString(strList.ToString());
    }

    public static SqlString SplitItem(string input, string separator, int idx)
    {
        string[] str = input.Split(new string[] { separator }, StringSplitOptions.RemoveEmptyEntries);
        return str.Length> idx ? str[idx] : "";
    }
    public static string GetJsonItem(string input, string key)
    {
        string pattern = string.Format("(?<=\"{0}\":\").*?(?=\")", key);
        return Regex.Match(input, pattern, RegexOptions.IgnoreCase).Value;
    }
}

三，在SQL Server中创建CLR函数

要在SQL Server的数据库中创建CLR函数，必须配置SQL Server的选项，使用DLL文件创建Assembly，并使用Assembly创建SQL 函数。

1，配置SQL Server

在SQL Server中创建CLR函数，需要配置数据库的高级选项：

exec sp_configure 'show advanced options', 1
go
reconfigure;
go
exec sp_configure 'clr strict security', 0;
go
reconfigure;
go
exec sp_configure 'clr enabled', 1
go
reconfigure
go

2，创建程序集

使用CLR Project生成的DLL文件来创建程序集：

CREATE ASSEMBLY [SQLServerDatabase]
FROM 'E:\clr_project_path.dll'
WITH PERMISSION_SET = SAFE
GO

3，从程序集创建SQL函数

CREATE FUNCTION [dbo].[Match](@input [nvarchar](max), @pattern [nvarchar](max))
RETURNS [nvarchar](max) WITH EXECUTE AS CALLER
AS
EXTERNAL NAME [SQLServerDatabase].[UserDefinedFunctions].[Match]
GO

CREATE FUNCTION [dbo].[IsMatch](@input [nvarchar](max), @pattern [nvarchar](max))
RETURNS bit WITH EXECUTE AS CALLER
AS
EXTERNAL NAME [SQLServerDatabase].[UserDefinedFunctions].[IsMatch]
GO

CREATE FUNCTION [dbo].[Matches](@input [nvarchar](max), @pattern [nvarchar](max))
RETURNS [nvarchar](max) WITH EXECUTE AS CALLER
AS
EXTERNAL NAME [SQLServerDatabase].[UserDefinedFunctions].[Matches]
GO

CREATE FUNCTION [dbo].[SplitItem](@input [nvarchar](max), @separator [nvarchar](max), @idx int)
RETURNS [nvarchar](max) WITH EXECUTE AS CALLER
AS
EXTERNAL NAME [SQLServerDatabase].[UserDefinedFunctions].[SplitItem]
GO

CREATE FUNCTION [dbo].[GetJsonItem](@input [nvarchar](max), @key [nvarchar](max))
RETURNS [nvarchar](max) WITH EXECUTE AS CALLER
AS
EXTERNAL NAME [SQLServerDatabase].[UserDefinedFunctions].[GetJsonItem]
GO

把函数创建之后，就可以像调用普通的用户自定义函数那样来调用CLR函数。

update [dbo].[DimProductPath]
set  ProductPath_ProductFamily=dbo.SplitItem(ProductPath,'/',0)
    ,ProductPath_ProductName=dbo.SplitItem(ProductPath,'/',1)
    ,ProductPath_ProductVersion=dbo.SplitItem(ProductPath,'/',2)
    ,ProductPath_SupportTopic=dbo.SplitItem(ProductPath,'/',3)
    ,ProductPath_SupportSubtopic=dbo.SplitItem(ProductPath,'/',4)

参考文档：

C#中正则表达式的使用

C# 正则表达式菜鸟教程

Regular Expression Language - Quick Reference

创建