验证 sql/oracle 中的电子邮件/邮政编码字段
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/787167/
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
validation on email / postcode fields in sql/oracle
提问by Helder Gramacho
Would be gratefull for some advice on the following - Is it possible to validate email and postcode fields through some kind of check constraint in the sql in oracle ? or this kind of thing as i suspect pl/sql with regular expressions ?
将不胜感激关于以下方面的一些建议 - 是否可以通过 oracle 中的 sql 中的某种检查约束来验证电子邮件和邮政编码字段？或者像我怀疑带有正则表达式的 pl/sql 那样的事情？
Thanks
谢谢
采纳答案by dpbradley
If you're only concerned with the US, there are several sources of zip codes that you can obtain in flat-file format and import into a table, and then apply a foreign key constraint in your addresses to that table.
如果您只关心美国，您可以以平面文件格式获取多个邮政编码来源并导入到表中，然后将地址中的外键约束应用于该表。
Email addresses can be matched against a regular expression (needs 10g or higher) to validate the format, but checking to see if they are actual addresses is a much more difficult task.
电子邮件地址可以与正则表达式（需要 10g 或更高版本）匹配以验证格式，但检查它们是否是实际地址是一项困难得多的任务。
回答by Neil Kodner
Here's the regexp syntax for an email address, including quotes
这是电子邮件地址的正则表达式语法，包括引号
'[a-zA-Z0-9._%-]+@[a-zA-Z0-9._%-]+\.[a-zA-Z]{2,4}'
So you can use regexp_like() in a where clause or regexp_substr() to check whether your field contains a valid email address. Here's an example-you'll see that the regexp_substr() returns NULL on the address missing the .domain, which fails the substring validation. From there you can build a check constraint around it, or enforce it using a trigger(yuck), etc.
因此，您可以在 where 子句或 regexp_substr() 中使用 regexp_like() 来检查您的字段是否包含有效的电子邮件地址。这是一个示例 - 您将看到 regexp_substr() 在缺少 .domain 的地址上返回 NULL，这导致子字符串验证失败。从那里您可以围绕它构建一个检查约束，或者使用触发器（yuck）等强制执行它。
SQL> desc email
Name Null? Type
----------------------------------------- -------- ----------------------------
EMAIL_ID NUMBER
EMAIL_ADDRESS VARCHAR2(128)
SQL> select * from email;
EMAIL_ID EMAIL_ADDRESS
---------- ----------------------------------------
1 [email protected]
2 [email protected]
3 [email protected]
4 bad_address@missing_domaindotorg
SQL> @qry2
SQL> column email_address format a40
SQL> column substr_result format a30
SQL> SELECT email_address
2 , regexp_substr(email_address,'[a-zA-Z0-9._%-]+@[a-zA-Z0-9._%-]+\.[a-zA-Z]{2,4}') substr_result
3 FROM email
4 /
EMAIL_ADDRESS SUBSTR_RESULT
---------------------------------------- ------------------------------
[email protected] [email protected]
[email protected] [email protected]
[email protected] [email protected]
bad_address@missing_domaindotorg
Using the same data, here is a query which limits only valid email addresses, using REGEXP_LIKE
使用相同的数据，这里是一个只限制有效电子邮件地址的查询，使用 REGEXP_LIKE
SQL> column email_address format a40
SQL> column substr_result format a30
SQL> SELECT email_address
2 FROM email
3 WHERE REGEXP_LIKE (email_address, '[a-zA-Z0-9._%-]+@[a-zA-Z0-9._%-]+\.[a-zA-Z]{2,4}');
EMAIL_ADDRESS
----------------------------------------
[email protected]
[email protected]
[email protected]
Search the contents page of the SQL Referencefor regexp to see the regular expression support.
在SQL Reference的内容页面中搜索regexp 以查看正则表达式支持。
回答by Helder Gramacho
an even better regular expression is:
更好的正则表达式是：
^[a-zA-Z0-9._%-]+@[a-zA-Z0-9._%-]+\.[a-zA-Z]{2,4}$
(same expression given but anchored to start (^) and end ($) of line)
（给出相同的表达式，但锚定到行的开始 ( ^) 和结束 ( $)）
without the anchors, expressions like '[email protected] some other text' would be validated and, if you are trying to validate one email, the above string should not validate
如果没有锚点，像“[email protected] some other text”这样的表达式将被验证，如果你试图验证一封电子邮件，上面的字符串不应该验证
Note: the email should be previously trimmed so that leading or trailing spaces won't screw up validation.
注意：电子邮件应该事先修剪，以便前导或尾随空格不会搞砸验证。
Hope it helps,
希望能帮助到你，
回答by Wilfried Loche
Be careful at the '.'character: this is a joker (like *or %in SQL syntax).
You must excape it with '\'.
小心这个'.'角色：这是一个小丑（像*或%在 SQL 语法中）。你必须用'\'.
Here is the regexp I use to match RFC2822(maybe not all cases :)):
这是我用来匹配RFC2822 的正则表达式（可能不是所有情况:)）：
'^[a-zA-Z0-9!#$%''\*\+-/=\?^_`\{|\}~]+@[a-zA-Z0-9._%-]+\.[a-zA-Z]{2,4}$'
回答by Jahanzeb Saqib
declare
-- where customer is the table in my case
email_input customer.email%type;
begin
email_input:=:EMAIL;
IF email_input is not null
AND email_input not like '%@%.COM' then
message('Please enter a valid email address!');
message(' ');
clear_message;
:EMAIL:=null;
raise form_trigger_failure;
end if;
end;
