How to Validate Email Address in SQL

In applications that take user email id as input, there is a need to check for email id validity. Here is a very easy validation for syntax of an email address, using regular expressions in Oracle SQL.

The basic email address format is username@example.com. The SQL will verify that the email address provided fits into that format. This can be used before data entry, or coded on a table as a check constraint.

How to Validate Email Address in SQL

Let’s go back to our old friends the superheroes from the article on regular expressions.

SQL> select * from t_superheros;

 ID NAME          EMAIL              PHONE
--- ------------- ------------------ ---------------
 1 Superman      superman@kmail.com (655) 981-4455
 2 Batman        batmankmail.com    6a5-981-4455
 3 Spiderman     spiderman@com      657-8154455
 4 Flash Gordon  flashgordon@com    (99) 981-4455
 5 Phantom       phantom.test@gmail.com  (999) 981-4455

As you can see, only Superman and Phantom have their email addresses in the right format. The others have either the ‘@’ or the ‘.’ missing or incorrectly placed.

We need to write a regular expression to check that the email address is of the form:

text @   text   . text

Text is represented by \w. To specify that the text is at least be 1 character long, we put a + sign in the end: \w+.

This matches alphanumeric characters and the underscore (_). We need to allow a match for dots (.) in the user name too, making sure that two consecutive dots do not appear. We put the escape character “\” before “.” to show that it must be matched literally and not used as a metacharacter. Grouped with the text following it, this gives us (\.\w+).

To specify zero or more matches, we follow (\.\w+) with *.

So our entire regex for the username is \w+(\.\w+)*+.

@   can be written into the regex as-is.

For the domain part i.e. the part after @, at least one “.” is mandatory sandwiched between two text pieces. And there could be longer extensions with more than one “.”, such as yahoo.co.uk. This can be matched with the regex \w+(\.\w+)+.

So the resulting regular expression for email address validation is
^\w+(\.\w+)*+@\w+(\.\w+)+$.

Putting it into a query:

SQL> select id
2             , name
3             , email
4   from t_superheros
5   where regexp_like
6               (email
7               ,'^\w+(\.\w+)*+@\w+(\.\w+)+$');

ID NAME                     EMAIL
---------- -------------- --------------------
1 Superman             superman@kmail.com
5 Phantom               phantom.test@gmail.com

Regular expression validation can be applied as a check constraint on the table itself, like this:

SQL> create table t_superheros
2   (
3         id           NUMBER
4     , name       VARCHAR2(14)
5     , email     VARCHAR2(20) CONSTRAINT email_format
6                         CHECK (REGEXP_LIKE (email
7                                 , '^\w+(\.\w+)*+@\w+(\.\w+)+$')
8                                     )
9     , phone     VARCHAR2(14)
10   );

Table created.

SQL> insert into t_superheros values
2   ( 1, 'Superman', 'superman@kmail.com', '(655) 981-4455');

1 row created.

SQL> insert into t_superheros values
2   ( 2, 'Batman', 'batmankmail.com', '6a5-981-4455');
insert into t_superheros values
*
ERROR at line 1:
ORA-02290: check constraint (HR.EMAIL_FORMAT) violated

SQL> insert into t_superheros values
2   ( 3, 'Spiderman', 'spiderman@com', '657-8154455');
insert into t_superheros values
*
ERROR at line 1:
ORA-02290: check constraint (HR.EMAIL_FORMAT) violated

SQL> insert into t_superheros values
2   ( 4, 'Flash Gordon', 'flashgordon@com', '(99) 981-4455');
insert into t_superheros values
*
ERROR at line 1:
ORA-02290: check constraint (HR.EMAIL_FORMAT) violated

SQL> insert into t_superheros values
2   ( 5, 'Phantom', 'phantom.test@gmail.com', '(999) 981-4455');

1 row created.

With this little piece of code, no invalid email format will be allowed entry into the t_superheros table.

Also check out the SQL for phone number validation.

Note: This post gives you the framework for standard email validation. RFC specifications may permit a selection of non-alphanumeric characters, comments, forwarding syntax etc. in the email address. The regex can be extended accordingly.