TSQL电子邮件验证（无正则表达式） [英] TSQL Email Validation (without regex)

问题描述

好吧，那里有一百万个用于验证电子邮件地址的正则表达式，但是可以将一些基本的电子邮件验证集成到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过程或函数。

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.

这篇关于TSQL电子邮件验证（无正则表达式）的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！