xp_pcre - T-SQL 中的正则表达式

• 下载源代码和 DLL - 219 Kb

引言

xp_pcre 是我之前发布的扩展存储过程 xp_regex 的后续版本。两者都允许您在 Microsoft SQL Server 2000 的 T-SQL 中使用正则表达式。此版本之所以写出来，是因为 xp_regex 使用 .NET Framework，而许多人不愿意在他们的 SQL Server 上安装 .NET Framework。（事实证明他们是明智的：尽管在 SQL Server 上安装 .NET Framework 并不会引起担忧，但我已被多人告知，在 SQL Server 进程内托管 CLR 是一个糟糕的主意™。有关更多信息，请参阅 xp_regex 页上的警告。）

xp_pcre 之所以命名为 xp_pcre，是因为它使用了“Perl 兼容正则表达式”库。该库可在 www.pcre.org 上找到。（您不需要下载 PCRE 库即可使用 xp_pcre。该库是静态链接的。）

概述

DLL 中包含六个扩展存储过程

• xp_pcre_match
• xp_pcre_match_count
• xp_pcre_replace
• xp_pcre_format
• xp_pcre_split
• xp_pcre_show_cache

所有这些过程的参数都可以是 CHAR、VARCHAR 或 TEXT，长度不限，只要是 SQL Server 支持的即可。唯一的例外是 xp_pcre_split 的 @column_number 参数，它是一个 INT。

如果任何必需参数为 NULL，则不会执行匹配，并且输出参数将设置为 NULL。（注意：这与以前的版本不同，以前的版本会将参数保留不变。）

1. xp_pcre_match

语法

EXEC master.dbo.xp_pcre_match @input, @regex, @result OUTPUT

• @input 是要检查的文本。
• @regex 是正则表达式。
• @result 是一个输出参数，它将包含 '0'、'1' 或 NULL。

xp_pcre_match 检查输入是否与正则表达式匹配。如果匹配，则 @result 将设置为 '1'。如果不匹配，则 @result 设置为 '0'。如果 @input 或 @regex 为 NULL，或发生异常，则 @result 将设置为 NULL。

例如，这将确定输入字符串是否包含至少两个连续的数字

DECLARE @out CHAR(1)
EXEC master.dbo.xp_pcre_match 'abc123xyz', '\d{2,}', @out OUTPUT
PRINT @out

打印输出

1

这个将确定输入字符串是否完全由至少两个连续的数字组成

DECLARE @out CHAR(1)
EXEC master.dbo.xp_pcre_match 'abc123xyz', '^\d{2,}$', @out OUTPUT
PRINT @out

打印输出

0

2. xp_pcre_match_count

语法

EXEC master.dbo.xp_pcre_match_count @input, @regex, @result OUTPUT

• @input 是要检查的文本。
• @regex 是正则表达式。
• @result 是一个输出参数，它将包含正则表达式匹配输入字符串的次数（或在输入为 NULL 和/或正则表达式无效的情况下为 NULL）。

xp_pcre_match_count 告诉您在输入字符串中找到了多少个不重叠的匹配项。将其作为单独的过程而不是 xp_pcre_match 的原因是为了效率。在 xp_pcre_match 中，一旦找到一个匹配项，该过程就可以返回。xp_pcre_match_count 需要不断尝试匹配，直到到达输入字符串的末尾。

例如，这将确定输入中一个独立的数字序列（长度不限）出现了多少次

DECLARE @out VARCHAR(20)
EXEC master.dbo.xp_pcre_match_count '123abc4567xyz', '\d+', @out OUTPUT
PRINT @out

打印输出

2

3. xp_pcre_replace

语法

EXEC master.dbo.xp_pcre_replace @input, @regex, @replacement, @result OUTPUT

• @input 是要解析的文本。
• @regex 是正则表达式。
• @replacement 是每个匹配项将被替换的内容。
• @result 是一个输出参数，它将包含结果。

xp_pcre_replace 是一个搜索和替换函数。所有匹配项都将被替换为 @replacement 参数的内容。

例如，这是如何删除输入字符串中的所有空格

DECLARE @out VARCHAR(8000)
EXEC master.dbo.xp_pcre_replace 'one  two    three four ', '\s+', '', @out OUTPUT
PRINT '[' + @out + ']'

打印输出

[onetwothreefour]

将所有数字（无论长度）替换为 "###"

DECLARE @out VARCHAR(8000)
EXEC master.dbo.xp_pcre_replace
   '12345 is less than 99999, but not 1, 12, or 123',
   '\d+',
   '###',
   @out OUTPUT

PRINT @out

打印输出

### is less than ###, but not ###, ###, or ###

支持捕获括号。然后，您可以使用变量 $1、$2、$3 等在替换字符串中使用捕获的文本。例如

DECLARE @out VARCHAR(8000)
EXEC master.dbo.xp_pcre_replace
   'one two three four five six seven',
   '(\w+) (\w+)',
   '$2 $1,',
   @out OUTPUT

PRINT @out

打印输出

two one, four three, six five, seven

如果您需要在替换字符串中包含字面量的 $，请用 \ 进行转义。另外，如果您的替换变量需要紧跟一个数字，则需要将变量号放在花括号中。${1}00 将导致第一个捕获后跟字面字符 00。例如

DECLARE @out VARCHAR(8000)
EXEC master.dbo.xp_pcre_replace
   '75, 85, 95',
   '(\d+)',
   '\$${1}00',
   @out OUTPUT

PRINT @out

打印输出

$7500, $8500, $9500

4. xp_pcre_format

语法

EXEC master.dbo.xp_pcre_format @input, @regex, @format, @result OUTPUT

• @input 是要匹配的文本。
• @regex 是正则表达式。
• @format 是格式字符串。
• @result 是一个输出参数，它将包含结果。

xp_pcre_format 的行为与 .NET 中的 Regex.Result() 或 Perl 中的字符串插值（即 $formatted_phone_number = "($1) $2-$3"）完全相同。

例如，正则表达式 (\d{3})[^\d]*(\d{3})[^\d]*(\d{4}) 将解析几乎任何您传入的 US 电话号码格式的字符串。

DECLARE @out VARCHAR(100)

DECLARE @regex VARCHAR(50)
SET @regex = '(\d{3})[^\d]*(\d{3})[^\d]*(\d{4})'

DECLARE @format VARCHAR(50)
SET @format = '($1) $2-$3'

EXEC master.dbo.xp_pcre_format
   '(310)555-1212',
   @regex,
   @format,
   @out OUTPUT
PRINT @out

EXEC master.dbo.xp_pcre_format
   '310.555.1212',
   @regex,
   @format,
   @out OUTPUT
PRINT @out

EXEC master.dbo.xp_pcre_format
   ' 310!555 hey! 1212 hey!',
   @regex,
   @format,
   @out OUTPUT
PRINT @out

EXEC master.dbo.xp_pcre_format
   ' hello, ( 310 ) 555.1212 is my phone number. Thank you.',
   @regex,
   @format,
   @out OUTPUT
PRINT @out

打印输出

(310) 555-1212
(310) 555-1212
(310) 555-1212
(310) 555-1212

捕获和转义约定与 xp_pcre_replace 相同。

5. xp_pcre_split

语法

EXEC master.dbo.xp_pcre_split @input, @regex, @column_number, @result OUTPUT

• @input 是要解析的文本。
• @regex 是匹配分隔符的正则表达式。
• @column_number 指定要返回的列。
• @result 是一个输出参数，它将包含格式化的结果。

列号从 1 开始。如果 @column_number 小于 1，则会引发错误。如果 @column_number 大于拆分产生的列数，则 @result 将设置为 NULL。

此函数按某种分隔符（逗号、管道符等）拆分文本数据。使用正则表达式进行拆分的优点在于，分隔符不必像您通常预期的那样一致。

例如，假设您的源数据是这一行

one ,two|three : four

在这种情况下，我们的分隔符是逗号、管道符或冒号，前面或后面（或两者）有任意数量的空格。用正则表达式表示就是：\s*[,|:]\s*。

例如

DECLARE @out VARCHAR(8000)

DECLARE @input VARCHAR(50)
SET @input = 'one  ,two|three  : four'

DECLARE @regex VARCHAR(50)
SET @regex = '\s*[,|:]\s*'

EXEC master.dbo.xp_pcre_split @input, @regex, 1, @out OUTPUT
PRINT @out

EXEC master.dbo.xp_pcre_split @input, @regex, 2, @out OUTPUT
PRINT @out

EXEC master.dbo.xp_pcre_split @input, @regex, 3, @out OUTPUT
PRINT @out

EXEC master.dbo.xp_pcre_split @input, @regex, 4, @out OUTPUT
PRINT @out

打印输出

one
two
three
four

6. xp_pcre_show_cache

语法

EXEC master.dbo.xp_pcre_show_cache

为了防止重复编译正则表达式，xp_pcre 会缓存它处理过的最后 50 个正则表达式。（查看RegexCache.h 的底部可以更改此硬编码值。）xp_pcre_show_cache 返回一个结果集，其中包含当前缓存中的所有正则表达式。在正常操作过程中，实际上没有必要使用它，但在开发过程中我发现它很有用。（我决定将其保留，因为它可能对任何想要通过它学习扩展存储过程编程的人有所帮助。）

7. fn_pcre_match, fn_pcre_match_count, fn_pcre_replace, fn_pcre_format 和 fn_pcre_split

这些是包装存储过程的用户定义函数。这样，您就可以将函数用作 SELECT 列表、WHERE 子句或任何其他可以使用表达式的地方（例如 CHECK 约束！）。对我来说，使用 UDF 是使用此库的一种更自然的方式。

USE pubs
GO

SELECT master.dbo.fn_pcre_format(
   phone,
   '(\d{3})[^\d]*(\d{3})[^\d]*(\d{4})',
   '($1) $2-$3'
   ) as formatted_phone
FROM
   authors

这将格式化“authors”表中的所有电话号码。

请注意，您需要在每个使用它们的数据库中创建 UDF，或者记住始终使用它们的完全限定名称（即 master.dbo.fn_pcre_format）来引用它们。或者，您可以遵循 bmoore86 在本页底部的帖子“You don't have to put the functions in every database”中的建议。

另请注意，SQL Server 中的用户定义函数在错误处理方面不是很健壮。如果 xp_pcre 返回错误，UDF 将会抑制它并返回 NULL。如果您正在使用 UDF 并且在意想不到的情况下得到 NULL，请尝试运行底层的存储过程。如果 xp_pcre 返回错误，您将能够看到它。

8. 安装

1. 将 xp_pcre.dll 复制到您的 \Program Files\Microsoft SQL Server\MSSQL\binn 目录。
2. 运行 SQL 脚本 INSTALL.SQL。这将注册过程并在 master 数据库中创建用户定义函数。
3. 如果您想运行一些基本的健全性检查/断言，请运行 TESTS.SQL 和 ERROR_TESTS.SQL。这些脚本也用于记录过程在无效输入情况下的行为。

9. Unicode 支持

不幸的是，此版本不支持 Unicode 参数。可能的解决方案包括

1. 使用 xp_regex。在内部，CLR 和 .NET Framework 是 100% Unicode 的。但是，由于在 SQL Server 进程内托管 CLR 可能存在问题，因此不建议使用此选项。
2. 使用 Boost Regex++ 库。不幸的是，这意味着放弃许多较新的正则表达式功能（零宽度断言、封闭式模式修饰符等）。
3. 让 xp_pcre 转换为 UTF-8，这在 PCRE 中是支持的。由于我不在 SQL Server 中使用 Unicode 数据，因此我没有实现它。我们将将其留给令人头疼的“留给读者的练习”。:)
4. 在 UDF 中使用 CAST、CONVERT 或隐式转换来强制参数为 ASCII。这可能对您不起作用，因为您之所以使用 NVARCHAR/NTEXT 列，就是因为您的数据无法用 ASCII 表示。

10. 杂项

要构建代码，您需要安装 Boost 库。您可以从 www.boost.org 下载。只需在 VS.NET 的项目属性中更改“附加包含目录”项。它位于“配置属性 | C/C++ | 常规”下。

欢迎提供评论/更正/补充。请随时给我发电子邮件……您可以在任何源代码文件的标题中找到我的电子邮件地址。谢谢！

11. 历史记录

• 2005年3月16日（v. 1.3.1）
  • 添加了 xp_pcre_match_count 和 fn_pcre_match_count。
• 2005年2月20日（v. 1.3）
  • 删除了所有 PCRE++ 代码并从头重写。它不是线程安全的，并且（在我看来）在拆分和替换时效率太低。这应该可以提高并发性（因为我不再需要对 PCRE 对象进行任何锁定）。另外，由于我从头开始，所以我能够使拆分和替换/格式化的行为更接近 Perl 的输出（尤其是在零宽度匹配的情况下）。
  • 添加了 xp_pcre_format。
  • 改进了参数验证和错误处理。
  • 更新了 TESTS.sql 并添加了 ERROR_TESTS.sql。
• 2005年2月14日（v. 1.2）
  • 修复了在正则表达式匹配零宽度字符串（例如 '\s*'）时导致 xp_pcre 无限循环的问题。
  • 错误条件现在将导致输出参数设置为 NULL。旧版本将保留原值不变。
  • 使用 pcrepp::Pcre 对象进行匹配现在受 CRITICAL_SECTION 保护。尽管 PCRE++ 对象可以重用，但它们似乎不是线程安全的。如果有人认为这会影响可伸缩性，请告诉我。我们可以修改缓存以允许相同正则表达式的多个实例。
  • 创建了 TESTS.sql 作为一种记录/验证正常和错误情况下预期结果的方法。
  • 此版本静态链接到 PCRE 5。前一个版本使用了 PCRE 4.3 DLL。我构建了 PCRE 的 Debug（pcre5_d.lib）和 Release（pcre5.lib）版本。xp_pcre 将在构建时链接到相应的版本。
  • 参数数据类型和大小都得到了更严格和更主动的检查。以前，我只是在尝试读写参数值时等待一个通用的失败错误。
  • 如果输出参数无法容纳整个结果，将向 SQL Server 返回一条错误消息，指示所需的变量大小。参数值将设置为 NULL。
  • 在适用的地方添加了 catch(...) 处理程序，以防止未处理的异常传播回 SQL Server。
• 2003年10月6日 - 更新了 ZIP 文件以包含 xp_pcre.dll。在杂项部分提到了 Boost 的要求。稍微清理了文档。
• 2003年8月10日 - 初始发布。