12 Replies Latest reply: Dec 4, 2012 6:04 AM by Troll35 RSS

    Email validation using Regular Expression.

    Nattu
      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
          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
            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
              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
                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
                  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
                    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
                      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
                        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
                          Thanks Alberto, for your solution and pointing out the issue. It just works fine.
                          • 10. Re: Email validation using Regular Expression.
                            Nattu
                            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
                              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
                                Hello Alberto,

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

                                Regards,
                                Troll35