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

Question

我有一些使用常规表达式的SQLCLR代码。但现在它正在迁移到Azure中，而Azure不允许SQLCLR，那就过时了。我需要找到一种在纯T-SQL中执行正则表达式的方法。

主数据服务不可用，因为我们的开发版本不是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(一次用于4个字母的TLD，如.info，然后再次用于6个字母的TLD，如.museum)。我经常听到非常有见识的人说，完美的电子邮件验证是不可能的，我以前在电子邮件服务提供商工作过，我可以告诉您，这是一个不断变化的目标。但对于最简单的方法，请参阅问题TSQL Email Validation (without regex)。

一个数字数字

可能是其中最简单的一个：

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

信用卡号码

假设你去掉了破折号和空格，这是你在任何情况下都应该做的。请注意，这并不是对信用卡号码算法的实际检查，以确保信用卡号码本身是有效的，只是它符合一般格式(AmEx = 15位以3开头，其余为16位- Visa以4开头，MasterCard以5开头，MasterCard以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个可能的值进行侵入性的模式匹配似乎有点过分了。类似地，对于1000个数字或年份的列表，检查数值是否在表中要容易得多(可能也更有效)，而不是将其转换为字符串并查看它是否与某种模式匹配。

我将再次声明，如果您能够在数据进入数据库之前对其进行清理和验证，那么这样做会更好。您应该尽可能地努力做到这一点，因为如果没有CLR，您就不能在SQL Server中实现强大的RegEx。

Answer 2

Ken Henderson写了关于replicate RegEx without CLR的方法，但它们需要sp_OA*过程，甚至比CLR更不可能在Azure中看到曙光。您可以在网上找到的大多数其他文章都使用类似于Ken的方法，或者使用内置字符串函数的复杂用法。

您特别想要复制RegEx的哪些部分？你能给出一个你的函数的输入/输出的例子吗？也许使用PATINDEX这样的内置字符串函数可以很容易地进行转换，以获得类似的结果。