SQL & PL/SQL

Hello Oracle Experts

Oracle Database 11g Enterprise Edition Release 11.1.0.7.0 - 64bit Production
With the Partitioning, OLAP, Data Mining and Real Application Testing options

I have some E-mail addresses syntax to be validated. I did some of my homework but I need some expert advice.

I have not yet completely serached the Oracle forum. If anyone has done or has lead into the validation of e-mail address please forward me the link. I will be thankful


With tab As
(
SELECT 'Jbond008@42@verizon.net' email From Dual Union All
SELECT 'jbond..007@yahoo.com' email From Dual Union All
SELECT 'jbond.008@yahoo..com' email From Dual Union All
SELECT 'Jbond009@hotm%%il.com' email From Dual Union All
SELECT 'jbond010@hotmail.2010' email From Dual Union All
SELECT '^*()@hotmail.co.in' email From Dual Union All
SELECT 'abc@hotmailcoin' email From Dual Union All
SELECT 'jbond012@Abc.office' email From Dual
)
select * from tab where
not REGEXP_LIKE (upper(trim(EMAIL)), '[A-Z0-9_%-]+@[A-Z0-9._%-]+\.[A-Z]{2,4}')
or regexp_count (upper(trim(EMAIL)),'@') > 1



The rules I need to validates are:

1. No two @ sign in a e-mail address
2. No two period (..) in an e-mail
3. After @ sign no special character or numeric character only Alphabets
4. Atleast one character before @ sign should be either alpha or Numeric
5. Atleast one period should be there after the @ sign
6. The last e-mail address (@Abc.office) should be valid



Thanks for the Help/Guidance

Rajesh

Edited by: 792498 on Sep 2, 2010 12:55 PM