Validation by email / zip fields in sql / oracle

Question

It would be appreciated for some advice on the following: is it possible to check email and zip fields through some kind of sql validation restriction in oracle? or this kind of thing, as I suspect, pl / sql with regular expressions?

thanks

Answer 1

If you are only interested in the United States, there are several sources of zip codes that can be obtained in a flat file format and imported into a table, and then apply the foreign key constraint in their addresses to this table.

( 10g ) , , , .

Answer 2

regexp ,

'[a-zA-Z0-9._%-]+@[a-zA-Z0-9._%-]+\.[a-zA-Z]{2,4}'

, regexp_like() where regexp_substr(), , . : , regexp_substr() NULL , .domain, . (yuck) ..

SQL> desc email
 Name                                      Null?    Type
 ----------------------------------------- -------- ----------------------------
 EMAIL_ID                                           NUMBER
 EMAIL_ADDRESS                                      VARCHAR2(128)


SQL> select * from email;

  EMAIL_ID EMAIL_ADDRESS
---------- ----------------------------------------
         1 [e-mail]
         2 [e-mail]
         3 [e-mail]
         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
---------------------------------------- ------------------------------
[e-mail]                           [e-mail]
[e-mail]                             [e-mail]
[e-mail]                     [e-mail]
bad_address@missing_domaindotorg

, , , 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
----------------------------------------
[e-mail]
[e-mail]
[e-mail]

SQL regexp, .

Answer 3

:

^[a-zA-Z0-9._%-]+@[a-zA-Z0-9._%-]+\.[a-zA-Z]{2,4}$

( , (^) end ($) )

, '[e-mail] - ' , ,

: , .

, ,

Answer 4

Be careful with the symbol '.': it is a joker (for example, *or %in SQL syntax). You must extract it with '\'.

Here is the regex that I use to match RFC2822 (maybe not in all cases :)):

'^[a-zA-Z0-9!#$%''\*\+-/=\?^_`\{|\}~]+@[a-zA-Z0-9._%-]+\.[a-zA-Z]{2,4}$'

Answer 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;