How to Validate Email Address in SQL

April 19, 2010

in DBA, SQL

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.

{ 8 comments… read them below or add one }

1 xavier August 26, 2011 at 8:05 pm

It looks like you used phone number validation in your query example for email validation.

2 oratabler August 26, 2011 at 9:10 pm

Hi xavier, Nicely spotted! Fixed now, thank you.

3 sam March 2, 2012 at 11:12 pm

Please note that \w does not include the ‘.’ symbol and thus this regex will fail on e-mails such as :

face.facertone@gmail.com

Which is a perfectly valid e-mail. Also, this regex doesn’t support + forwarding, which is part of the e-mail specification (things like: face.facertone+superheroes@gmail.com).

While this regex will work for _some_ subset of allowable e-mails, it will incorrectly invalidate valid (as per the RFC) e-mail addresses.

A better regex:

‘^[\w+\-.]+@[\w\d\-.]+\.[a-z]+$’

4 oratabler March 3, 2012 at 8:54 am

Fair point, sam. Thanks for your comment. I have updated the post to accommodate the ‘.’ symbol in the local part.

Things like forwarding, comments in the email address, non-alphanumerics would be beyond the scope of a startup guide for beginners IMO. I have added a note to this effect in the post.

5 saroj October 11, 2012 at 11:40 pm

this code is correct but when u i’ll remove the space then it i’ll execute.
i got help from this code thanx alot..

6 Sriram January 10, 2013 at 6:59 pm

Sam,
The regex ‘^[\w+\-.]+@[\w\d\-.]+\.[a-z]+$’ given ora-12728: invalid range in regular expression.

Tried the below and got the error, i tried that by oratabler as well and it works good for all except “face.facertone+superheroes@gmail.com”.

select count(1)
from dual
where regexp_like (‘face.facertone+superheroes@gmail.com’ ,’^[\w+\-.]+@[\w\d\-.]+\.[a-z]+$’)

7 Grant August 6, 2013 at 11:47 pm

Sam,

I’ve also noticed this code rejects emails that have a “-” in them.

Ex: face.factertone@gmail-eu.com would be rejected. Even though “-” is allowed in emails.

I’m working on a way to fix that and will post on here when I do.

Thanks,
Grant

8 Binh Thanh Nguyen June 14, 2016 at 11:28 am

Thanks, nice tips

Leave a Comment

Previous post:

Next post: