TSQL 电子邮件验证(无正则表达式)

2021-12-10 00:00:00 tsql sql-server email-validation

好的，有上百万个正则表达式可用于验证电子邮件地址，但如何将一些基本的电子邮件验证集成到 Sql Server 2005 的 TSQL 查询中?

Ok, there are a million regexes out there for validating an email address, but how about some basic email validation that can be integrated into a TSQL query for Sql Server 2005?

我不想使用 CLR 过程或函数.只是直接的 TSQL.

I don't want to use a CLR procedure or function. Just straight TSQL.

有人已经解决了吗?

推荐答案

非常:

SELECT
  EmailAddress, 
  CASE WHEN EmailAddress LIKE '%_@_%_.__%' 
            AND EmailAddress NOT LIKE '%[any obviously invalid characters]%' 
  THEN 'Could be' 
  ELSE 'Nope' 
  END Validates
FROM 
  Table

这匹配中间有 @ 的所有内容，前面至少有一个字符，后跟至少两个字符，一个点，至少两个用于 TLD.

This matches everything with an @ in the middle, preceded by at least one character, followed by at least two, a dot and at least two for the TLD.

您可以编写更多 LIKE 模式来做更具体的事情，但是您永远无法匹配可能是电子邮件地址的所有内容，同时又不会漏掉不是电子邮件地址的内容.即使使用正则表达式，您也很难做到正确.此外，即使根据 RFC 的字母进行匹配，也会匹配大多数电子邮件系统不会接受/使用的地址结构.

You can write more LIKE patterns that do more specific things, but you will never be able to match everything that could be an e-mail address while not letting slip through things that are not. Even with regular expressions you have a hard time doing it right. Additionally, even matching according to the very letters of the RFC matches address constructs that will not be accepted/used by most emailing systems.

无论如何，在数据库级别执行此操作可能是错误的方法，因此如上所述的基本健全性检查可能是您在性能方面所能获得的最佳结果，而在应用程序中执行此操作将为您提供更大的灵活性.

Doing this on the database level is maybe the wrong approach anyway, so a basic sanity check as indicated above may be the best you can get performance-wise, and doing it in an application will provide you with far greater flexibility.

相关文章