regex - T-SQL 中的 SQL Server 正则表达式解决方法？

Question

我有一些 SQLCLR 代码用于处理正则表达式。但是现在它正在迁移到不允许 SQLCLR 的 Azure 中，这就结束了。我需要找到一种在纯 T-SQL 中执行正则表达式的方法。

Master Data Services 不可用，因为我们拥有的 MSSQL 开发版不是 R2。

感谢所有的想法，谢谢。

正则表达式匹配需要处理的样本
(过去几年从regexlib和其他地方剔除)

电子邮件地址

^[\w-]+(\.[\w-]+)*@([a-z0-9-]+(\.[a-z0-9-]+)*?\.[a-z]{2,6}|(\d{1,3}\.){3}\d{1,3})(:\d{4})?$

美元

^(\$)?(([1-9]\d{0,2}(\,\d{3})*)|([1-9]\d*)|(0))(\.\d{2})?$

uri

^(http|https|ftp)\://([a-zA-Z0-9\.\-]+(\:[a-zA-Z0-9\.&%\$\-]+)*@)*((25[0-5]|2[0-4][0-9]|[0-1]{1}[0-9]{2}|[1-9]{1}[0-9]{1}|[1-9])\.(25[0-5]|2[0-4][0-9]|[0-1]{1}[0-9]{2}|[1-9]{1}[0-9]{1}|[1-9]|0)\.(25[0-5]|2[0-4][0-9]|[0-1]{1}[0-9]{2}|[1-9]{1}[0-9]{1}|[1-9]|0)\.(25[0-5]|2[0-4][0-9]|[0-1]{1}[0-9]{2}|[1-9]{1}[0-9]{1}|[0-9])|localhost|([a-zA-Z0-9\-]+\.)*[a-zA-Z0-9\-]+\.(com|edu|gov|int|mil|net|org|biz|arpa|info|name|pro|aero|coop|museum|[a-zA-Z]{2}))(\:[0-9]+)*(/($|[a-zA-Z0-9\.\,\?\'\\\+&%\$#\=~_\-]+))*$

一位数字

^\d$

百分比

^-?[0-9]{0,2}(\.[0-9]{1,2})?$|^-?(100)(\.[0]{1,2})?$

高度符号

^\d?\d'(\d|1[01])"$

1 1000 之间的数字

^([1-9]|[1-9]\d|1000)$

信用卡号码

^((4\d{3})|(5[1-5]\d{2})|(6011))-?\d{4}-?\d{4}-?\d{4}|3[4,7]\d{13}$

年表

^([1-9]{1}[0-9]{3}[,]?)*([1-9]{1}[0-9]{3})$

一周中的日子

^(Sun|Mon|(T(ues|hurs))|Fri)(day|\.)?$|Wed(\.|nesday)?$|Sat(\.|urday)?$|T((ue?)|(hu?r?))\.?$

12 小时制时间

(?<Time>^(?:0?[1-9]:[0-5]|1(?=[012])\d:[0-5])\d(?:[ap]m)?)

24 小时制时间

^(?:(?:(?:0?[13578]|1[02])(\/|-|\.)31)\1|(?:(?:0?[13-9]|1[0-2])(\/|-|\.)(?:29|30)\2))(?:(?:1[6-9]|[2-9]\d)?\d{2})$|^(?:0?2(\/|-|\.)29\3(?:(?:(?:1[6-9]|[2-9]\d)?(?:0[48]|[2468][048]|[13579][26])|(?:(?:16|[2468][048]|[3579][26])00))))$|^(?:(?:0?[1-9])|(?:1[0-2]))(\/|-|\.)(?:0?[1-9]|1\d|2[0-8])\4(?:(?:1[6-9]|[2-9]\d)?\d{2})$

美国电话号码

^\(?[\d]{3}\)?[\s-]?[\d]{3}[\s-]?[\d]{4}$

Answer 1

不幸的是，您将无法将 CLR 函数移动到 SQL Azure。您将需要使用普通字符串函数(PATINDEX、CHARINDEX、LIKE 等)或在数据库之外执行这些操作。

编辑 为添加到问题的示例添加一些信息。

邮箱地址

这个总是有争议的，因为人们对他们想要支持的 RFC 版本存在分歧。例如，原版不支持撇号(或者至少人们坚持认为它不支持 - 我没有从文件中挖掘出来并自己阅读，诚然)，并且必须经常扩展以用于新的TLD(一次用于 .info 等 4 个字母的 TLD，然后再次用于 .museum 等 6 个字母的 TLD)。我经常听到知识渊博的人说完美的电子邮件验证是不可能的，而且我以前在电子邮件服务提供商工作过，我可以告诉你，这是一个不断变化的目标。但对于最简单的方法，请参阅问题 TSQL Email Validation (without regex) .

一位数字

可能是最简单的一个:

WHERE @s LIKE '[0-9]';

信用卡号

假设你去掉了破折号和空格，无论如何你都应该这样做。请注意，这不是对信用卡号码算法的实际检查以确保号码本身实际上有效，只是它符合一般格式(AmEx = 15 位以 3 开头，其余为 16 位 - Visa以 4 开头，MasterCard 以 5 开头，Discover 以 6 开头，我认为有一个以 7 开头(尽管这可能只是某种礼品卡)):

WHERE @s + ' ' LIKE '[3-7]'+ REPLICATE('[0-9]', 14) + '[0-9 ]';

如果你想以冗长的代价更精确一点，你可以说:

WHERE (LEN(@s) = 15 AND @s LIKE '3'     + REPLICATE('[0-9]', 14))
   OR (LEN(@s) = 16 AND @s LIKE '[4-7]' + REPLICATE('[0-9]', 15));

美国电话号码

同样，假设您要先去掉括号、破折号和空格。很确定美国区号不能以 1 开头；如果有其他规则，我不知道。

WHERE @s LIKE '[2-9]' + REPLICATE('[0-9]', 9);

-----

我不会更进一步，因为您定义的许多其他表达式都可以从上面推断出来。希望这能给你一个开始。您应该能够在 Google 上搜索其他一些人，以了解其他人如何使用 T-SQL 复制这些模式。其中一些(如一周中的几天)可能只是根据表格进行检查 - 对一组 7 个可能的值进行 invasie 模式匹配似乎有点过分。与包含 1000 个数字或年份的列表类似，这些事情会更容易(并且可能更有效)来检查数值是否在表中，而不是将其转换为字符串并查看它是否与某种模式匹配。

我将再次声明，如果您可以在数据进入数据库之前对其进行清理和验证，那么其中的很多内容都会好得多。您应该尽可能地努力做到这一点，因为如果没有 CLR，您就无法在 SQL Server 中执行强大的 RegEx。