validation on email / postcode fields in sql/oracle

Question

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

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

Answer 2

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 [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

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 Reference for regexp to see the regular expression support.