sqloracleregexp-like

How match email address with regex expression?

the match to find email has wrong regex expression, leading to missing emails that should have been otherwise populated in the output.

Below is the query

case when regexp_like(email,'^([a-zA-Z0-9_\-\.]+)@([a-zA-Z0-9_\-\.]+)\.([a-zA-Z]{2,5})$')

The above regex expression doesn't match emails like BILL@AMERITRUST-MORTGAGE.COM Instead it returns null when such emails are encountered.

Solution

  • I used this statement and it worked:

    select 1
  from dual
 where regexp_like('BILL@AMERITRUST-MORTGAGE.COM', '^([a-zA-Z0-9_\.-]+)@([a-zA-Z0-9_\.-]+)\.([a-zA-Z]{2,5})$');

    The minus character must be the last one in the [...].