This discussion is archived
12 Replies Latest reply: Dec 4, 2012 4:04 AM by troll35 RSS

Email validation using Regular Expression.

Nattu Explorer
Currently Being Moderated
Hi,

I am in need of using a regular expression for email valiatino.
^[a-z0-9!#$%&'*+/=?^_`{|}~-]+(?:\.[a-z0-9!#$%&'*+/=?^_`{|}~-]+)*@(?:[a-z0-9](?:[a-z0-9-]*[a-z0-9])?\.)+(?:[A-Z]{2}|com|org|net|edu|gov|mil|biz|info|mobi|name|aero|asia|jobs|museum)$ 
which is gotten from http://www.regular-expressions.info/email.html .

This works fine in a regular expression test tool http://regexpal.com/ , but when I use in Oracle, it does not.
DECLARE 
v_exp VARCHAR2(4000);

BEGIN
   --v_exp := '^[a-z0-9!#$%&''*+/=?^_`{|}~-]+(?:\.[a-z0-9!#$%&''*+/=?^_`{|}~-]+)*@(?:[a-z0-9](?:[a-z0-9-]*[a-z0-9])?\.)+(?:[A-Z]{2}|com|org|net|edu|gov|mil|biz|info|mobi|name|aero|asia|jobs|museum)$';
   v_exp := '^[a-z0-9!#\$%&''*+/=?\^_`{|}~-]+(?:\.[a-z0-9!#\$%&''*+/=?\^_`{|}~-]+)*@(?:[a-z0-9](?:[a-z0-9-]*[a-z0-9])?\.)+(?:[A-Z]{2}|com|org|net|edu|gov|mil|biz|info|mobi|name|aero|asia|jobs|museum)$';
   dbms_output.put_line(v_exp);
   
   FOR v_rec IN (
      with test_data as
( select 'address@oracle.co.uk'     as val from dual union all
  select 'address@oracle.com'       as val from dual union all
  select 'ad{ress@foo.oracle.co.uk' as val from dual union ALL
  select 'ad.ress@foo.oracle.co.uk' as val from dual union all  
  select 'ad1234ress@foo.oracle.co.uk' as val from dual union all  
  select 'address@oracle..com'      as val from dual union all
  select 'no#good'                  as val from dual
)
select
  val , 
  case
    when
      regexp_like
      (val, v_exp,'i') then 'Y'
    else 'N'
    end
    as good
from test_data ) LOOP
   dbms_output.put_line(v_rec.val||', '||v_rec.good);
END LOOP;   
END;   
and the results are

address@oracle.co.uk, N
address@oracle.com, N
ad{ress@foo.oracle.co.uk, N
ad.ress@foo.oracle.co.uk, N
ad1234ress@foo.oracle.co.uk, N
address@oracle..com, N
no#good, N

The expected result should be Y for all but for the last two.

I am not sure why it is not working in Oracle.

Can any body please help me to find the mistake and to have it correctly?

Advance Thanks,

Natarajan

Edited by: Nattu on Dec 4, 2012 1:31 AM                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                       
  • 1. Re: Email validation using Regular Expression.
    stefan nebesnak Journeyer
    Currently Being Moderated
    Try this regular expression: >> ^[_a-z0-9-]+(\.[_a-z0-9-]+)*@[a-z0-9-]+(\.[a-z0-9-]+)*(\.[a-z]{2,4})$ >> address@oracle.co.uk, *Y* >> address@oracle.com, *Y* >> ad{ress@foo.oracle.co.uk, N >> ad.ress@foo.oracle.co.uk, *Y* >> ad1234ress@foo.oracle.co.uk, *Y* >> address@oracle..com, N >> no#good, N [Using a regular expression to validate an email address|http://stackoverflow.com/questions/201323/using-a-regular-expression-to-validate-an-email-address] Edited by: stefan nebesnak on 4.12.2012 2:25                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                           
  • 2. Re: Email validation using Regular Expression.
    Purvesh K Guru
    Currently Being Moderated
    I think this pretty matches your requirements; Let me know if it does not. I tested with your data and got expected results:
    with data as
    (
    select 'address@oracle.co.uk'     as val from dual union all
      select 'address@oracle.com'       as val from dual union all
      select 'ad{ress@foo.oracle.co.uk' as val from dual union ALL
      select 'ad.ress@foo.oracle.co.uk' as val from dual union all  
      select 'ad1234ress@foo.oracle.co.uk' as val from dual union all  
      select 'address@oracle..com'      as val from dual union all
      select 'no#good'                  as val from dual union all
      select 'address@oracle..'      as val from dual
    )
    select val, case when regexp_like(val, '^[[:alpha:]]+[[:alnum:].{}-]+@[[:alpha:]]+\.{1}[[:alpha:]]+') then 'Y' else 'N' end res  from data;
    
    VAL                         RES 
    --------------------------- --- 
    address@oracle.co.uk        Y   
    address@oracle.com          Y   
    ad{ress@foo.oracle.co.uk    Y   
    ad.ress@foo.oracle.co.uk    Y   
    ad1234ress@foo.oracle.co.uk Y   
    address@oracle..com         N   
    no#good                     N   
    address@oracle..            N
  • 3. Re: Email validation using Regular Expression.
    Nattu Explorer
    Currently Being Moderated
    Thanks Stefan and Purvesh, for your help. I have this working expression, I want to have the expression to check the domain also. Example it should return address@oracle.com as valid but adsdfs@adfsd.dsfsfd as invalid.
  • 4. Re: Email validation using Regular Expression.
    troll35 Pro
    Currently Being Moderated
    Hello,

    You can use this one :
    [a-z0-9!#\$%&''*+/=?^_`{|}~-]+(?:\.[a-z0-9!#\$%&''*+/=?^_`{|}~-]+)*@([A-Z0-9-]+\.)*([A-Z]{2}|com|org|net|edu|gov|mil|biz|info|mobi|name|aero|asia|jobs|museum)$
    It gives this :
    address@oracle.co.uk, Y
    address@oracle.com, Y
    ad{ress@foo.oracle.co.uk, Y
    ad.ress@foo.oracle.co.uk, Y
    ad1234ress@foo.oracle.co.uk, Y
    address@oracle..com, N
    no#good, N
    Regards
  • 5. Re: Email validation using Regular Expression.
    AlbertoFaenza Expert
    Currently Being Moderated
    Hi Nattu,

    the reason why it is not working is because you have to remove the oracle unsupported '?:' which simply means in Regular expression not to create back reference. I think you can simply omit this.

    This regular expression should fit your needs:
       v_exp := '^[a-z0-9!#\$%&''*+/=?\^_`{|}~-]+(\.[a-z0-9!#\$%&''*+/=?\^_`{|}~-]+)*@([a-z0-9]([a-z0-9-]*[a-z0-9])?\.)+([A-Z]{2}|com|org|net|edu|gov|mil|biz|info|mobi|name|aero|asia|jobs|museum)$';
    
    Output:
    ^[a-z0-9!#\$%&'*+/=?\^_`{|}~-]+(\.[a-z0-9!#\$%&'*+/=?\^_`{|}~-]+)*@([a-z0-9]([a-z0-9-]*[a-z0-9])?\.)+([A-Z]{2}|com|org|net|edu|gov|mil|biz|info|mobi|name|aero|asia|jobs|museum)$
    address@oracle.co.uk, Y
    address@oracle.com, Y
    ad{ress@foo.oracle.co.uk, Y
    ad.ress@foo.oracle.co.uk, Y
    ad1234ress@foo.oracle.co.uk, Y
    address@oracle..com, N
    no#good, N
    It's important to keep the flag 'i' in regular expression (as you are doing) to ignore case otherwise you need to modify your expression to support all possible cases or use UPPER or LOWER function on your input string and change the regular expression accordingly.

    Regards.
    Al
  • 6. Re: Email validation using Regular Expression.
    Marwim Expert
    Currently Being Moderated
    address@oracle.com as valid but adsdfs@adfsd.dsfsfd as invalid.
    Do you want to check against a list of possible top level domains?
    While the example above
    (?:[A-Z]{2}|com|org|net|edu|gov|mil|biz|info|mobi|name|aero|asia|jobs|museum)
    covers most of the now available TLDs it will work only until ~ 2014. Then ICANN is likely to introduce hundreds of new generic TLDs like free, music, shop, berlin, wien and nyc, see http://en.wikipedia.org/wiki/Top-level_domain.

    Then a lot of valid addresses will be marked a invalid.

    Regards
    Marcus
  • 7. Re: Email validation using Regular Expression.
    TPD-Opitz-Consulting-com Expert
    Currently Being Moderated
    Nattu wrote:
    Thanks Stefan and Purvesh, for your help. I have this working expression, I want to have the expression to check the domain also. Example it should return address@oracle.com as valid but adsdfs@adfsd.dsfsfd as invalid.
    The problem with this one is that it is valid according to the rules of the email standard (http://tools.ietf.org/html/rfc5321#page-16 point 2.3.11)
    It is invalid becaus (today) there is no domain adfsd.dsfsfd (but this may change in future).
    You can't catch this with a reguar expression. (For those having a hammer only: this is not a nail!)

    You need a black-list to compare the addresses to in order to catch this ones.

    bye
    TPD
  • 8. Re: Email validation using Regular Expression.
    Nattu Explorer
    Currently Being Moderated
    Thanks Troll, yours and Alberto's solutions work for me. I wish I had marked both as correct answers, but it accepted only one as correct answer, so I made yours as helpful (never mind it). Thanks a lot again for your help and time.
  • 9. Re: Email validation using Regular Expression.
    Nattu Explorer
    Currently Being Moderated
    Thanks Alberto, for your solution and pointing out the issue. It just works fine.
  • 10. Re: Email validation using Regular Expression.
    Nattu Explorer
    Currently Being Moderated
    Thanks Marcus for your fore warning, it is an important warning to note. For the moment, we need to use the solution, I am sure that we will revisit to it. Thanks again.

    Edited by: Nattu on Dec 4, 2012 3:27 AM
  • 11. Re: Email validation using Regular Expression.
    AlbertoFaenza Expert
    Currently Being Moderated
    Troll35 wrote:
    Hello,

    You can use this one :
    [a-z0-9!#\$%&''*+/=?^_`{|}~-]+(?:\.[a-z0-9!#\$%&''*+/=?^_`{|}~-]+)*@([A-Z0-9-]+\.)*([A-Z]{2}|com|org|net|edu|gov|mil|biz|info|mobi|name|aero|asia|jobs|museum)$
    Hi Troll,

    without the initial anchor ^ it will recognize this address as valid:
    ....aa@oracle.com, Y
    And if you put the initial anchor then it will recognize this as invalid:
    ad.ress@foo.oracle.co.uk, N
    I have explained in my post what the problem was.

    Regards.
    Al
  • 12. Re: Email validation using Regular Expression.
    troll35 Pro
    Currently Being Moderated
    Hello Alberto,

    You're right, I've missed that. Thanks for pointing it.

    Regards,
    Troll35

Legend

  • Correct Answers - 10 points
  • Helpful Answers - 5 points