Sind years i have a simple Emai-check in my Toolbox. It is a simple PL/SQL function within a package. Two weeks ago i came about this regexp-page. There you can find a regexp that implements the RFC 2822 standard, or an more practical approach of it. Since a time regular expressions are supported in PL/SQL, so i implemented it in my little email-check. I don't understand a lot of regexp and the regexp did not work out of the box, so a colleague helped me to correct and implement it for PL/SQL. The problem was a missing ^ at the start and the ?: in the regexp. The results are excellent! The regexp checks all official domains that are longer than 2 characters and also checks if the other domain are not longer than 2 characters. Give it a try!
Here is my code:
-- %author Manfred Hofbauer (fairtec.at)
-- {*}CreateDate: 07.05.2006
-- {*}purpose: function checks for an valid email (contains @ and ....)
-- %version 0.3
-- {*}ChangeHistory
-- {*}When Who Version purpose
-- {*}07.05.2006 Manfred Hofbauer 0.1 created
-- {*}03.12.2007 Manfred Hofbauer 0.2 changed to regexp
-- {*}05.12.2007 Manfred Hofbauer 0.3 returns number to be usable in SQL
-- %param pEmail String that shall be checked for an valid email
-- %return true or false (1=TRUE 0=False)
-- %raises
CREATE OR REPLACE FUNCTION isValidEmail (pEmail IN VARCHAR2) RETURN NUMBER IS
cEmailRegexp CONSTANT VARCHAR2(1000) := '^[a-z0-9!#$%&''*+/=?^_`{|}~-]+(\.[a-z0-9!#$%&''*+/=?^_`{|}~-]+)*@([a-z0-9]([a-z0-9-]*[a-z0-9])?\.)+([A-Z]{2}|arpa|biz|com|info|intww|name|net|org|pro|aero|asia|cat|coop|edu|gov|jobs|mil|mobi|museum|pro|tel|travel|post)$';
BEGIN
IF REGEXP_LIKE(pEmail,cEmailRegexp,'i') THEN
RETURN 1;
ELSE
RETURN 0;
END IF;
-- !!!!DONT FORGET YOUR ERROR-HANDLING HERE!!!!
END isValidEmail;
/