在 SQL 中使用 CLR 过程的 RegEx

如果在 SQL 代码中可以使用正则表达式 (RegEx) 该有多方便？ 您可以通过能够指定正则表达式而不是通常使用的简单 like % 匹配，来执行一些非常高级的查询。

SQL CLR 可以做到这一点！

是的，您可以使用 .NET 正则表达式库，通过注册为 SqlFunction 的 SQL CLR 方法来实现。 此示例使用 VistaDB 4，但通过更改 using 命名空间，相同的代码也可以与 SQL Server 2005 / 2008 配合使用。 我将在稍后的博文中演示在 SQL Server 中的相同代码。

示例 SQL 查询

完成之后，查询将如下所示。 这将选择所有 ShipPostalCode 恰好为 5 位数字的订单（没有字母，没有空格等）。

SELECT * FROM ORDERS WHERE LIKEREGEX
                 ( ShipPostalCode,  '^[0-9][0-9][0-9][0-9][0-9]$') = 1

CLR 方法

这是我将用于 SQL REGEX 的完整 C# 方法。 它非常简单，但这就是 .NET 的强大之处。 想象一下仅使用 SQL 编写正则表达式解析器... 没门！

[SqlFunction]
public static bool RegExMatch(string inputValue, string regexPattern )
{
    // Any nulls - we can't match, return false
    if (string.IsNullOrEmpty(inputValue) || string.IsNullOrEmpty(regexPattern))
        return false;
    
    Regex r1 = new Regex(regexPattern.TrimEnd(null));
    return r1.Match(inputValue.TrimEnd(null)).Success;
}

将程序集加载到数据库的 SQL

以下 SQL 代码可以在 Data Builder 中运行，并用于使用我们想要的名称注册程序集和函数。 请参阅示例项目中的实际 vsql4 文件，了解良好的错误处理方式，以及如何在程序集已存在于数据库中时更新程序集。

CREATE ASSEMBLY [RegExCLR] FROM 'RegExCLR.dll';

-- Add the REGEX function.  We want a friendly name 
-- LIKEREGEX rather than the full namespace name.
-- Note the way we have to specify the Assembly.Namespace.Class.Function
-- NOTE the RegExCLR.RegExCLR 
-- (one is the assembly the other is the namespace)
CREATE FUNCTION LIKEREGEX ( @inputCalue NVARCHAR(4000), 
    @regexPattern NVARCHAR(4000) ) RETURNS BIT
    AS EXTERNAL NAME RegExCLR.RegExCLR.ClrClass.RegExMatch;

现在我们已经注册了它，就可以调用它了。 请查看示例调用脚本，了解如何调用该例程的示例。

在 45 分钟内完成 CLR 过程

整个示例的构建时间少于 45 分钟。 我希望它能激励您寻找更多在数据库中使用 CLR 的方法。 整个代码可以在 VistaDB Public Downloads 网站上找到。

观看 YouTube 视频

我录制了一段视频，演示我逐步执行上述步骤，并演示代码在 VistaDB 中的工作方式。 您也可以直接前往 YouTube 观看使用 Regex 的 CLR 过程示例 (HD)。