validation on email / postcode fields in sql/oracle

Hi,

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 ?

Thanks

From stackoverflow

• 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.

• 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.

  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

  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.

  Pourquoi Litytestdata : That regexp won't work for email addresses that use the .museum and .travel TLDs.