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 }
It looks like you used phone number validation in your query example for email validation.
Hi xavier, Nicely spotted! Fixed now, thank you.
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]+$’
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.
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..
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]+$’)
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
Thanks, nice tips