SQL Server 2005 的 T-SQL 正则表达式库
4.81/5 (38投票s)
本文向读者展示了如何为 SQL Server 2005 构建一个标量和表值函数库以执行正则表达式分析。
引言
随着 CLR 集成到 SQL Server 2005 中，扩展 T-SQL 编程语言的功能变得异常容易。CLR 集成可以改进的两个领域是字符串匹配和字符串操作。
背景
T-SQL 有少量基本的字符串匹配函数（例如CHARINDEX、PATINDEX、SOUNDEX）和字符串匹配运算符（例如=、<>、<、>、LIKE）。这些对于诸如在电子邮件地址列上创建检查约束以确保只存在有效电子邮件地址的任务来说是不够的。验证此类字符串的最常用方法是使用正则表达式。这就是 CLR 集成发挥作用的地方。
SQL Server 2005 现在允许您使用您选择的 .NET 语言创建用户定义函数（以及其他功能）。本文将演示如何使用 C# 开发一套用于 T-SQL 的通用用户定义正则表达式函数。
Using the Code
通用方法
我的目标是将 .NET Framework 中 RegEx 类的一些常用静态方法封装成可在 T-SQL 环境中使用的东西。我认为最好的方法是将其开发为一组用户定义函数，这些函数与 RegEx 类中这些方法的输入和输出紧密对应。
接口
本文列出的所有四个函数共享相同的前两个参数
@Input NVARCHAR(MAX)
这是要分析的字符串。您可以传递字面值字符串或列名。这是将执行正则表达式的字符串。
@Pattern NVARCHAR(MAX)
这是将针对@Input参数执行的正则表达式。
此外，所有四个函数共享相同的最后一个参数。
@IgnoreCase BIT
这是一个布尔参数，当设置为“1”时，它将指示正则表达式引擎在针对输入字符串执行正则表达式时忽略大小写。如果此参数设置为“0”，将执行区分大小写的分析。
函数
ufn_RegExIsMatch
此函数的目的是模仿 Regex.IsMatch 方法的功能。简而言之，如果在 @Input 指定的字符串中找到 @Pattern 参数中指定的模式，则返回值为“1”。否则，返回值为“0”。
ufn_RegExMatches
虽然 ufn_RegExIsMatch 会告诉您 @Input 中是否存在 @Pattern 的匹配项，但此函数会告诉您匹配项是什么，它们在字符串中的位置以及每个匹配项的长度。此函数封装了 .NET Framework 中 Regex.Matches 方法的功能。此函数返回一个表。此表的列如下所示
Match NVARCHAR(MAX)
MatchIndex INT
MatchLength INT
ufn_RegExReplace
此函数模仿了 .NET Framework 中 Regex.Replace 的功能，并且与 T-SQL 中的 REPLACE 函数的功能非常相似。ufn_RegExReplace 和 REPLACE 之间的主要区别在于，REPLACE 中的匹配完全基于字面字符串匹配比较，而 ufn_RegExReplace 基于 @Pattern 参数中指定的正则表达式进行匹配。
ufn_RegExReplace 还带有一个此库中其他函数都不包含的额外参数，即 @Replacement 参数。这是另一个 NVARCHAR(MAX) 参数，它指定了在执行 @Pattern 时用于替换 @Input 中识别的匹配项的字面值字符串。
此函数返回一个 NVARCHAR(MAX)，表示已进行指定替换的输入字符串。
ufn_RegExSplit
将带分隔符的字符串拆分为其元素是 T-SQL 开发人员的常见任务。ufn_RegExSplit 函数正是如此。此实现使用 .NET Framework 中的 Regex.Split 方法。ufn_RegExSplit，很像 ufn_RegExMatches 函数，返回一个表作为其输出。然而，此表只有一列，其数据类型为 NVARCHAR(MAX)。列名为 Match，它包含由 @Pattern 中指定的分隔符拆分出的字符串元素。
注意：我意识到对于大多数实现，简单的分隔符拆分就足够了，对于这种情况，我建议使用一个类似的使用 String.Split 方法而不是正则表达式的函数。然而，由于本文的重点是 T-SQL 中的正则表达式，我决定坚持使用正则表达式实现。
SQL Server 实现
现在程序集已经创建，我们需要将其实现到 SQL Server 中。以下步骤概述了该过程。
确定程序集将驻留的数据库
程序集是数据库级对象，而不是服务器级对象。您需要选择将包含程序集的数据库
--Set the database to which these functions will be installed
use AdventureWorks
GO
启用 CLR 集成
CLR 集成默认是禁用的。要启用它，您必须运行 sp_configure 并将“clr enabled”属性设置为 1。然后，您必须发出“RECONFIGURE”命令以使设置生效。否则，设置直到 SQL Server 重启后才会生效。
--Enable CLR Integration
exec sp_configure 'clr enabled', 1
GO
RECONFIGURE
GO
创建程序集
启用 CLR 集成后，下一步是通过从实际编译的 DLL 文件中导入来“创建程序集”。SQL Server 将程序集作为字节流存储在数据库内部。一旦使用“CREATE ASSEMBLY”命令导入程序集，您就不再需要实际的 DLL 文件。
CREATE ASSEMBLY [SqlRegEx] FROM 'C:\SqlRegEx.dll' WITH PERMISSION_SET = SAFE
创建函数
创建程序集后，您可以创建实际的函数。这样做的语法与创建 T-SQL 函数非常相似。主要区别在于，您无需编写函数体，只需指定“AS EXTERNAL NAME”，后跟 [AssemblyName].[Namespace.Class].[Method]，其中 [AssemblyName] 是 SQL Server 中的程序集名称，而 Namespace、Class 和 Method 都指程序集内部的命名空间、类和方法。
CREATE FUNCTION [dbo].[ufn_RegExIsMatch]
(@Input NVARCHAR(MAX), @Pattern NVARCHAR(MAX), @IgnoreCase BIT)
RETURNS BIT
AS EXTERNAL NAME SqlRegEx.[SqlClrTools.SqlRegEx].RegExIsMatch
调用函数
此时，函数已加载并准备就绪。要调用它们，您只需使用与调用任何其他 T-SQL 函数相同的语法。
SELECT dbo.ufn_RegExIsMatch('Hello World', 'w', 1) --Ignores Case
SELECT dbo.ufn_RegExIsMatch('Hello World', 'w', 0) --Case Sensitive
关注点
PERMISSION_SET
在 SQL Server 中创建程序集时，您必须指定程序集所需的权限集。
在大多数情况下，您会像本文一样选择“SAFE”。这意味着您不直接访问磁盘、网络等系统资源。这是最严格、最安全的权限集。
如果您需要访问磁盘、网络或其他资源，您可能需要使用“EXTERNAL_ACCESS”权限集。
在极少数情况下，您可能需要使用“UNSAFE”。此权限集授予 EXTERNAL_ACCESS 授予的所有内容，并允许访问非托管代码。使用此设置时请务必谨慎。
Unicode 和 NVARCHAR
所有 .NET 字符串都以 Unicode 处理。因此，进出这些函数的所有字符串数据都是 NVARCHAR 数据类型。我选择使用“MAX”长度，因为我们不知道传递给这些函数的字符串有多大。当然，您可能希望在您自己的实现中限制此长度。
属性
您可能会注意到 SqlRegEx 类中包含的每个方法都带有某些属性。以下列表将概述每个属性的用途。
DataAccess
DataAccess 属性向 SQL Server 发出信号，表明此方法是否会访问当前 SQL Server 实例上的任何用户数据。对于这些函数，它们都没有直接访问，因此此属性的值对于每个函数都是 false。
IsDeterministic
引用 SQL Server 联机丛书：“确定性函数在每次使用一组特定的输入值调用时，以及在给定相同的数据库状态时，总是返回相同的结果。非确定性函数在每次使用一组特定的输入值调用时，即使它们访问的数据库状态保持不变，也可能返回不同的结果。”本文列出的所有函数都是确定性的，因此此属性已标记为 true。
IsPrecise
IsPrecise 属性指示结果是否精确。例如，使用 FLOAT 数据类型计算值的函数将不精确，因为 FLOAT 数据类型在计算过程中可能会丢失信息。此库中的函数是精确的，因此，此属性为每个函数标记为 true。
名称
Name 属性包含一个字符串，指示此函数在 SQL Server 中注册时应调用的名称。此字段不是必需的，在 SQL Server 中没有实际价值，但我为这些函数中的每一个都设置了它，以供我个人记录。
SystemDataAccess
SystemDataAccess 属性与 DataAccess 属性非常相似，不同之处在于它指的是系统数据而不是用户数据。
FillRowMethodName
FillRowMethodName 仅为返回表的函数设置。您会注意到，对于每个返回表的函数，.NET 代码中的实际返回类型都是 IEnumerable。这意味着您从该函数返回的任何内容都必须实现 IEnumerable 接口。IEnumerable 接口允许 .NET Framework 遍历您的结果，并且对于结果集中的每个结果，它将调用 FillRowMethodName 属性中列出的函数。如果您查看 FillRowMethodName 属性中列出的函数，您会发现它们每个都接受一个对象输入参数，该参数表示上述循环中的当前元素。其余参数是输出参数，它们将表示输出到 SQL Server 的表的列。
关于这种特定设置，有一点让我感到困扰，那就是您似乎不能将 System.Collections.Generic.IEnumerable 用作函数的返回类型。相反，您必须使用 System.Collections.IEnumerable。这意味着您必须为填充行方法的第一个参数接受一个对象，而不是能够指定实际类型。这当然意味着每次创建新行时都会进行装箱和拆箱操作。由于泛型在 .NET 2.0 中可用，我认为在这里允许它们是合适的。
确定性 vs. 非确定性
值得注意的是，如果您计划在计算列中使用本文中列出的任何标量函数，并且希望持久化该计算列，则该函数必须标记为“确定性”。您无法持久化包含任何非确定性内容的计算列。如果您无法持久化您的列，您将无法对其应用任何索引，并且每次调用时都会重新计算它。如果您的函数确实是确定性的，您将希望确保它不会每次都重新计算，因为根据定义，确定性函数总是返回相同的信息，您将浪费 CPU 周期。
摘要
CLR 是 SQL Server 开发人员可用的强大新工具。它几乎将整个 .NET Framework 开放给 SQL Server，如果使用得当，可以为任何 SQL Server 应用程序带来巨大的能力和价值。