验证sql / oracle中的电子邮件/邮政编码字段

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

5 个解决方案

#1

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或更高)匹配以验证格式,但检查它们是否是实际地址是一项更加困难的任务。

#2

Here's the regexp syntax for an email address, including quotes

这是电子邮件地址的regexp语法,包括引号

'[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 NEIL@GMAIL.COM
         2 JOE@UTAH.GOV
         3 lower_name@lower.org
         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
---------------------------------------- ------------------------------
NEIL@GMAIL.COM                           NEIL@GMAIL.COM
JOE@UTAH.GOV                             JOE@UTAH.GOV
lower_name@lower.org                     lower_name@lower.org
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
----------------------------------------
NEIL@GMAIL.COM
JOE@UTAH.GOV
lower_name@lower.org

Search the contents page of the SQL Reference for regexp to see the regular expression support.

在SQL参考的内容页面中搜索regexp以查看正则表达式支持。

#3

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 'test1@hotmail.com some other text' would be validated and, if you are trying to validate one email, the above string should not validate

如果没有锚点,“test1@hotmail.com”等表达式将被验证,如果您尝试验证一封电子邮件,则上述字符串不应验证

Note: the email should be previously trimmed so that leading or trailing spaces won't screw up validation.

注意:应先修剪电子邮件,以便前导或尾随空格不会搞砸验证。

Hope it helps,

希望能帮助到你,

#4

Be careful at the '.' character: this is a joker (like * or % in SQL syntax). You must excape it with '\'.

小心'。' character:这是一个小丑(在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}$'

#5

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;

#1