T-SQL：检查电子邮件格式
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/423606/
Warning: these are provided under cc-by-sa 4.0 license. You are free to use/share it, But you must attribute it to the original authors (not me):
StackOverFlow
T-SQL: checking for email format
提问by jerbersoft
I have this scenario where I need data integrity in the physical database. For example, I have a variable of @email_address VARCHAR(200)and I want to check if the value of @email_addressis of email format. Anyone has any idea how to check format in T-SQL?
我有这样一个场景，我需要物理数据库中的数据完整性。例如，我有一个变量，@email_address VARCHAR(200)我想检查它的值@email_address是否为电子邮件格式。任何人都知道如何在 T-SQL 中检查格式？
Many thanks!
非常感谢！
回答by splattne
I tested the following query with many different wrong and valid email addresses. It should do the job.
我使用许多不同的错误和有效电子邮件地址测试了以下查询。它应该完成这项工作。
IF (
CHARINDEX(' ',LTRIM(RTRIM(@email_address))) = 0
AND LEFT(LTRIM(@email_address),1) <> '@'
AND RIGHT(RTRIM(@email_address),1) <> '.'
AND CHARINDEX('.',@email_address ,CHARINDEX('@',@email_address)) - CHARINDEX('@',@email_address ) > 1
AND LEN(LTRIM(RTRIM(@email_address ))) - LEN(REPLACE(LTRIM(RTRIM(@email_address)),'@','')) = 1
AND CHARINDEX('.',REVERSE(LTRIM(RTRIM(@email_address)))) >= 3
AND (CHARINDEX('.@',@email_address ) = 0 AND CHARINDEX('..',@email_address ) = 0)
)
print 'valid email address'
ELSE
print 'not valid'
It checks these conditions:
它检查这些条件：
- No embedded spaces
- '@' can't be the first character of an email address
- '.' can't be the last character of an email address
- There must be a '.' somewhere after '@'
- the '@' sign is allowed
- Domain name should end with at least 2 character extension
- can't have patterns like '.@' and '..'
- 没有嵌入空格
- “@”不能是电子邮件地址的第一个字符
- '.' 不能是电子邮件地址的最后一个字符
- 必须有一个'.' '@' 之后的某处
- 允许使用“@”符号
- 域名应以至少 2 个字符的扩展名结尾
- 不能有像“.@”和“..”这样的模式
回答by annakata
AFAIK there is no good way to do this.
AFAIK 没有好的方法可以做到这一点。
The email format standard is so complex parsers have been known to run to thousands of lines of code, but even if you were to use a simpler form which would fail some obscure but valid addresses you'd have to do it without regular expressions which are not natively supported by T-SQL (again, I'm not 100% on that), leaving you with a simple fallback of somethign like:
众所周知，电子邮件格式标准非常复杂，解析器可以运行数千行代码，但即使您要使用更简单的形式，这会使一些晦涩但有效的地址失败，您也必须在没有正则表达式的情况下这样做T-SQL 本身不支持（同样，我不是 100% 支持），让你有一个简单的后备，比如：
LIKE '%_@_%_.__%'
LIKE '%_@_%_.__%'
..or similar.
..或类似的。
My feeling is generally that you shouln't be doing this at the last possible moment though (as you insert into a DB) you should be doing it at the first opportunity and/or a common gateway (the controller which actually makes the SQL insert request), where incidentally you would have the advantage of regex, and possibly even a library which does the "real" validation for you.
我的感觉通常是你不应该在最后一刻这样做（当你插入数据库时）你应该在第一次机会和/或公共网关（实际上使 SQL 插入的控制器请求），顺便说一句，您将拥有正则表达式的优势，甚至可能是一个为您进行“真实”验证的库。
回答by Rad
If you use SQL 2005 or 2008 you might want to look at writing CLR stored proceudues and use the .NET regex engine like this. If you're using SQL 2000 or earlier you can use the VBScript scripting engine's regular expression like ths. You could also use an extended stored procedure like this
如果您使用 SQL 2005 或 2008，您可能需要考虑编写 CLR 存储过程并像这样使用 .NET 正则表达式引擎。如果您使用 SQL 2000 或更早版本，则可以使用 VBScript 脚本引擎的正则表达式，如 ths。你也可以使用扩展存储过程是这样
回答by no_one
There is no easy way to do it in T-SQL, I am afraid. To validate all the varieties of email address allowed byRFC 2822you will need to use a regular expression.
恐怕在 T-SQL 中没有简单的方法可以做到这一点。要验证RFC 2822允许的所有电子邮件地址种类，您需要使用正则表达式。
More info here.
更多信息在这里。
You will need to define your scope, if you want to simplify it.
如果您想简化它，您将需要定义您的范围。
