MS SQL Server 2005/2008 中的正则表达式
4.84/5 (20投票s)
介绍如何使用 SQL Server CLR 集成创建正则表达式的 SQL 包装器
引言
正则表达式功能在 MS SQL Server 2005/2008 中可用。您可以通过 MS SQL Server CLR 集成使用所有 .NET Framework 的正则表达式功能。
本文档介绍了如何创建和使用支持正则表达式的 LIKE (Transact-SQL) 子句的扩展。仅为演示，我们还创建了一个文本解析器，该解析器可以根据给定的正则表达式模式从文本中提取标记。此外，还概述了使用 .NET Framework 公共语言运行时 (CLR) 的 Microsoft SQL Server 集成编译数据库对象所需的命名空间和库。
背景
如果您了解 T-SQL 和 C#，上述材料可能会有所帮助。在这种情况下，您可以利用丰富的库功能。如果您只掌握 T-SQL（不掌握 C#），则在需要正则表达式功能的地方，可以将 RegExpLike 函数替换为标准的 LIKE 子句。
性能
有关性能方面的说明，请参阅 MSDN 网站文章。
开发人员应将 CLR 视为查询语言中无法声明性表达的逻辑的有效替代方案。例如，正则表达式。
Microsoft 建议在 CLR 编程可以补充 T-SQL 查询语言的表达能力的情况下使用 CLR 集成。例如，需要在查询中嵌入过程逻辑，然后可以将其作为函数（LIKE 函数）调用。这包括以下情况：
- 在数据库表中存储的值上逐行执行复杂计算（必须使用过程逻辑来表达）。这可能涉及将这些计算的结果发送给客户端，或使用这些计算来筛选发送给客户端的行集。
- 使用过程逻辑来评估表格式结果，然后这些结果将在
SELECT或 DML 语句的FROM子句中进行查询。SQL Server 2000 引入了 T-SQL 函数（标量和表值函数），以支持这些场景。使用 SQL Server 2005，这些函数可以使用 CLR 语言更轻松地编写，因为开发人员可以利用 .NET Framework 中更丰富的库。此外，CLR 编程语言提供了 T-SQL 中缺乏的丰富数据结构（如数组、列表等），并且由于 CLR 和 T-SQL 的不同执行模型，可以显著提高性能。
函数通常是使用 CLR 编写的理想选择，因为函数很少需要访问数据库：通常将来自数据库的值作为参数传递。这发挥了 **CLR 的优势，它在计算任务方面比 T-SQL 更强**。
Using the Code
第一部分. LIKE 子句的扩展
首先，您必须允许 MS SQL Server 使用 CLR 集成，即启用 .NET 程序集及其方法的用法（默认情况下，此功能是禁用的）。为此，请使用以下脚本：
sp_configure 'clr enabled', 1
GO
RECONFIGURE
GO
如果您想恢复到默认状态，请运行此脚本：
sp_configure 'clr enabled', 0
GO
RECONFIGURE
GO
从这里，我们创建一个程序集，它是正则表达式 .NET 类的包装器。要使用 C#/.NET 为 MS SQL Server 创建用户定义函数，只需创建一个库项目，创建一个类，然后添加将在将来成为 SQL 函数的 `public static` 方法。此外，SqlFunctionAttribute 必须放在这些方法中的每一个前面。它用于将用户定义聚合函数的定义标记为 SQL Server 中的函数。对于我们的 RegularExpressionLike 方法，我们有一个如下所示的方法：
/// <summary>
/// Class that allows to support regular expressions
/// in MS SQL Server 2005/2008
/// </summary>
public partial class SqlRegularExpressions
{
/// <summary>
/// Checks string on match to regular expression
/// </summary>
/// <param name="text">string to check</param>
/// <param name="pattern">regular expression</param>
/// <returns>true - text consists match one at least,
/// false - no matches</returns>
[SqlFunction]
public static bool Like(string text, string pattern)
{
Match match = Regex.Match(text, pattern);
return (match.Value != String.Empty);
}
//...
}
下一步是程序集的构建。从现在开始，您必须将给定的程序集部署到 MS SQL Server。为此，请运行以下脚本（但您必须指出程序集在您机器上的路径）：
CREATE ASSEMBLY
--assembly name for references from SQL script
SqlRegularExpressions
-- assembly name and full path to assembly dll,
-- SqlRegularExpressions in this case
from 'd:\Projects\SqlRegularExpressions\
SqlRegularExpressions\bin\Release\SqlRegularExpressions.dll'
WITH PERMISSION_SET = SAFE
搞定！您的程序集已注册，现在我们可以使用它的功能了。这正是我们计划要做的。
顺便说一句，要撤销此操作，可以运行以下脚本：
drop assembly
--assembly name for references from SQL script
SqlRegularExpressions
要将程序集方法与 SQL 函数绑定，您必须运行如下所示的脚本：
--function signature
CREATE FUNCTION RegExpLike(@Text nvarchar(max), @Pattern nvarchar(255)) RETURNS BIT
--function external name
AS EXTERNAL NAME SqlRegularExpressions.SqlRegularExpressions.[Like]
就这样。现在，您可以使用 RegExpLike 函数来检查字符串是否与正则表达式模式匹配：
-- get all titles where title consists word that starts by 'A'
select * from titles
where 1 = dbo.RegExpLike(title, '\b(A\S+)')
运行上述脚本的结果如下：
这是删除脚本的函数：
DROP FUNCTION RegExpLike
第二部分. 文本解析
接下来的内容是使用正则表达式模式从给定文本中提取字符串。
这是一个获取表的任务，即函数执行的结果是一个包含某些数据的表。假设它包含文本中的起始索引、长度以及结果字符串的值。总共有三列在结果表中。CLR 为表值函数提供了一个流式处理模型，该模型确保在第一个行可用后即可立即使用结果，而无需等待整个表填充完毕。这给最终用户和性能带来了很多好处，当然，这也增加了实现的复杂性。用户定义表值函数需要实现两个 `public static` 方法：一个是 MS SQL Server 调用并返回对象枚举（IEnumerable）的主方法，另一个是由第一个方法调用以填充表行的辅助方法。如果我们查看下面的代码，一切都会变得清晰：
/// <summary>
/// Class that allows to support regular expressions
/// in MS SQL Server 2005/2008
/// </summary>
public partial class SqlRegularExpressions
{
// this is place of Like() method
/// <summary>
/// Gets matches from text using pattern
/// </summary>
/// <param name="text">text to parse</param>
/// <param name="pattern">regular expression pattern</param>
/// <returns>MatchCollection</returns>
[SqlFunction(FillRowMethodName="FillMatch")]
public static IEnumerable GetMatches(string text, string pattern)
{
return Regex.Matches(text, pattern);
}
/// <summary>
/// Parses match-object and returns its parameters
/// </summary>
/// <param name="obj">Match-object</param>
/// <param name="index">TThe zero-based starting
/// position in the original string where the captured
/// substring was found</param>
/// <param name="length">The length of the captured substring.</param>
/// <param name="value">The actual substring
/// that was captured by the match.</param>
public static void FillMatch(object obj, out int index,
out int length, out SqlChars value)
{
Match match = (Match)obj;
index = match.Index;
length = match.Length;
value = new SqlChars(match.Value);
}
}
GetMatches 返回所有匹配的元素作为 MatchCollection 对象，而 FillMatch 为之前获得的每个对象（Match）调用，以根据其中的数据确定表行的字段。我们可以从参数列表中看到这一点：第一个是对象引用，其余的是标记为 out 属性的变量。这个“其余部分”决定了可能的列的性质。
请注意！ 您必须在 SqlFunction.FillRowMethodName 属性中指定 Fill 方法的名称；另一方面，它也提供了一些灵活性。
现在，我们构建程序集，再次将其注册到 MS SQL Server，因为它的强名称在构建过程中被更改了，然后创建一个目标表值函数：
CREATE FUNCTION
--function signature
RegExpMatches(@text nvarchar(max), @pattern nvarchar(255))
RETURNS TABLE
([Index] int, [Length] int, [Value] nvarchar(255))
AS
--external name
EXTERNAL NAME SqlRegularExpressions.SqlRegularExpressions.GetMatches
GO
现在，我们可以通过特定模式从某些文本中提取字符串。例如，让我们获取文本中所有以小写字母 'a' 开头的单词：
-- RegExpMatches sample
DECLARE @Text nvarchar(max);
DECLARE @Pattern nvarchar(255);
SET @Text =
'This is comprehensive compendium provides a broad and thorough investigation of all '
+ 'aspects of programming with ASP.Net. Entirely revised and updated for the 2.0 '
+ 'Release of .Net, this book will give you the information you need to master ASP.Net '
+ 'and build a dynamic, successful, enterprise Web application.';
SET @Pattern = '\b(a\S+)'; --get all words that start from 'a'
select * from dbo.RegExpMatches(@Text, @Pattern)
GO
您将得到如下结果：
请注意，该脚本 **不** 返回任何 'ASP.NET' 标记，因为它们以大写字母 'A' 开头。如果您想在匹配时忽略大小写，则需要一个新函数，或者只需向 Regex.Matches() 方法添加一个额外的参数，如下所示：
[SqlFunction(FillRowMethodName="FillMatch")]
public static IEnumerable GetMatches(string text, string pattern)
{
return Regex.Matches(text, pattern, RegexOptions.IgnoreCase);
}
现在，RegExpMatches 返回所有以 'a' 开头的单词，包括 'ASP.NET'。
这是 RegExpMatches 函数的删除脚本：
DROP FUNCTION RegExpMatches
SQL 正则表达式，版本 2
SqlServerProject 下载包含一个“SQL Server 项目”类型的解决方案，而不是之前创建的“类库”类型的解决方案。对于开发数据库对象而言，这是一种更自然的项目类型。项目中包含几个文件。我们使用它们是因为在“类库”类型的项目中进行“删除/创建程序集/函数”的单调操作很无聊。“SQL Server 项目”类型假设所有这些操作无需人工参与。此外，它的 IDE 包含用于执行数据库对象任务的特殊菜单项。但是，只有拥有 VS Pro/Team 时才能享受它。
易于部署。我们需要三个步骤才能将正则表达式部署到我们的 SQL Server：
- 设置项目以引用目标数据库。
- 构建项目。
- 将支持 SQL Server 中正则表达式的程序集部署到我们的数据库引擎。
现在，一步一步来：
为了设置一个项目以引用一个适合使用正则表达式强大功能的数据库，我们这样做（请参见下图）：
- 我们在解决方案资源管理器窗口中右键单击项目。
- 我们选择“属性”菜单项。程序集属性窗口将展开。
- 在左侧，我们选择一个标题为“数据库”的书签。
- 我们单击“浏览...”按钮。“添加数据库引用”窗口将出现。
- 接下来，我们从列表中选择一个现有的数据库引用或添加新的。为此，我们单击“添加新引用...”按钮。“新建数据库引用”对话框将显示。
- 我们从相应的下拉列表中选择服务器名称和数据库名称。
- 然后，我们单击“确定”——“添加新引用”对话框将关闭。
- 我们再次单击“确定”，“添加数据库引用”对话框将关闭，并且设置了程序集部署的新目标。
现在是最后的操作了：
- 通过从“生成”菜单中选择“生成 SqlRegularExpressions”来生成项目（如果未更改，我们可以跳过此步骤，因为程序集已编译）。
- 从同一个“生成”菜单中选择“部署 SqlRegularExpressions”。
这是我们得到的结果：
========== Deploy: 1 succeeded, 0 failed, 0 skipped ==========
如果您没有 Visual Studio IDE，您可以在 RegExpLike 函数的“代码使用”部分中按照所述的方式安装正则表达式功能。程序集 DLL 位于 SqlServerProject 包的“Bin”文件夹中。还有一件事，这是 RegexMatch 函数的脚本：
--function signature
CREATE FUNCTION RegexMatch(@Text nvarchar(max),
@Pattern nvarchar(255), @Options INT) RETURNS BIT
--function external name
AS EXTERNAL NAME SqlRegularExpressions.UserDefinedFunctions.RegexMatch
程序集内容文档
RegexMatch, RegexStrMatch
在指定的输入字符串中搜索 Regex 构造函数中指定的正则表达式的第一个匹配项，并返回成功（RegexMatch）或匹配的字符串（RegexStrMatch）。
语法
RegexMatch(<input>, <pattern>, <options>) RETURNS BIT
RegexStrMatch(<input>, <pattern>, <options>)
RETURNS nvarchar(max)
参数
- input 要测试匹配的字符串。
- pattern 要匹配的正则表达式模式。
- options 枚举值的按位 OR 组合。有关此参数的某些值，请参见下表。
返回值
RegexMatch：如果输入字符串与模式匹配，则为 True，否则为 False。RegexStrMatch：匹配正则表达式的字符串，或者在不存在时为null。
备注
options 参数的值
| 选项 | C# RegexOptions 枚举成员的类似项 | 描述 |
|---|---|---|
0 - 或 - null |
无 |
指定不设置选项。 |
| 1 | IgnoreCase |
指定不区分大小写的匹配。 |
| 2 | Multiline |
多行模式。更改 ^ 和 $ 的含义，使其分别匹配任何行的开头和结尾，而不仅仅是整个字符串的开头和结尾。 |
| 4 | ExplicitCapture |
指定唯一有效的捕获是显式命名或编号的组，形式为 (?<name>...)。这允许未命名的括号充当非捕获组，而无需使用 (?:...) 表达式的语法笨拙。 |
| 8 | Compiled |
指定正则表达式被编译成程序集。这会提高执行速度，但会增加启动时间。调用 CompileToAssembly 方法时，不应将此值分配给 Options 属性。 |
| 16 | Singleline |
指定单行模式。更改点 (.) 的含义，使其匹配每个字符（而不是除 \n 之外的每个字符）。 |
| 32 | IgnorePatternWhitespace |
消除模式中未转义的空白字符，并启用以 # 开头的注释。但是，IgnorePatternWhitespace 值不会影响或消除字符类中的空白字符。 |
| 64 | RightToLeft |
指定搜索方向是从右到左，而不是从左到右。 |
| 256 | ECMAScript |
为表达式启用符合 ECMAScript 的行为。此值只能与 IgnoreCase、Multiline 和 Compiled 值结合使用。将此值与任何其他值一起使用将导致异常。 |
| 512 | CultureInvariant |
指定忽略语言的文化差异。 |
有关更多信息，请参阅 MSDN 文档中关于 Regex.Match 方法的内容。
示例
A. 以下代码获取所有标题，其中标题由以 'A' 或 'a' 开头的单词组成。
select * from titles
where dbo.RegexMatch(title, '\b(A\S+)', 1) = 1
B. 有时您只需要字符串的特定部分。您可以使用 Transact-SQL 来完成此提取，但也可以使用正则表达式。以下代码演示了如何按姓氏的最后一个词对作者姓名（“pubs”数据库）进行排序，前提是姓氏由多个词组成，例如“del Castillo”、“de Balzak”、“al Mahdi”等。
select au_id, (au_fname + ' ' + au_lname) au_name
from authors
order by
dbo.RegexStrMatch(au_lname, '(\w+)', 64)
结果是作者的排序列表，其中“Innes del Castillo”排在“Michel DeFrance”之前。
源代码
using System; //String
using System.Data.SqlTypes; //SqlString, SqlInt32, SqlBoolean
using System.Text.RegularExpressions; //Match, Regex
using Microsoft.SqlServer.Server; //SqlFunctionAttribute
public partial class UserDefinedFunctions
{
/// <summary>
/// Searches the input string for an occurrence
/// of the regular expression supplied
/// in a pattern parameter with matching options
/// supplied in an options parameter.
/// </summary>
/// <param name="input">The string to be tested for a match.</param>
/// <param name="pattern">The regular expression pattern to match.</param>
/// <param name="options">A bitwise OR combination
/// of RegexOption enumeration values.</param>
/// <returns>true - if inputted string matches
/// to pattern, else - false</returns>
/// <exception cref="System.ArgumentException">
/// Regular expression parsing error.</exception>
[SqlFunction(Name="RegexMatch", IsDeterministic=true, IsPrecise=true)]
public static SqlBoolean RegexMatch(SqlString input,
SqlString pattern, SqlInt32 options)
{
if (input.IsNull)
{
return SqlBoolean.Null;
//if input is NULL, return NULL
}
if (pattern.IsNull)
{
pattern = String.Empty;
//""
}
if (options.IsNull)
{
options = 0; //RegexOptions.None
}
try
{
Match match = Regex.Match((string)input,
(string)pattern, (RegexOptions)(int)options);
if (match.Value != String.Empty)
{
return SqlBoolean.True;
}
}
catch
{
throw;
}
return SqlBoolean.False;
}
};
如果您希望我补充某些功能，请告知我！谢谢！
关注点
这只是可以包装的几个方法。我已准备好扩展这个“SQL 正则表达式库”。**我邀请大家作为规范编写者参与进来。如果您在这里找不到所需的内容，可以随时给我留言，我会尽力满足您的要求**。
欢迎所有评论、建议和意见！
历史
- 2009 年 9 月 30 日：文章创建。
- 2009 年 10 月 7 日：添加了“性能”部分。
- 2009 年 10 月 8 日：添加了 SQL 正则表达式，版本 2（SQL Server 项目）。
- 2009 年 10 月 11 日：添加了“
RegexStrMatch”函数。