Regular expression is not working for email address validation from DB2 database

Md. Abdul Hannan 0
Viewed 31

I am using the following query to select valid email address from DB2 database.

Select CM_ID, CM_EMAIL from table_info where REGEXP_LIKE (CM_EMAIL,'^[A-Za-z]+[A-Za-z0-9.]+@[A-Za-z0-9.-]+\.[A-Za-z]{2,4}$')
regexp-like db2
1 Answers

The real correct regular expression is much more complex.
I have no idea if the answer at the How can I validate an email address using a regular expression? link is correct, but it has got too many votes, and it seems, that Db2 supports this quite complex expression at least. You may check it in the form like below and inform us, if it really works for you.

SELECT 
  CM_EMAIL
, REGEXP_LIKE (CM_EMAIL, '(?:[a-z0-9!#$%&''*+/=?^_`{|}~-]+(?:\.[a-z0-9!#$%&''*+/=?^_`{|}~-]+)*|"(?:[\x01-\x08\x0b\x0c\x0e-\x1f\x21\x23-\x5b\x5d-\x7f]|\\[\x01-\x09\x0b\x0c\x0e-\x7f])*")@(?:(?:[a-z0-9](?:[a-z0-9-]*[a-z0-9])?\.)+[a-z0-9](?:[a-z0-9-]*[a-z0-9])?|\[(?:(?:(2(5[0-5]|[0-4][0-9])|1[0-9][0-9]|[1-9]?[0-9]))\.){3}(?:(2(5[0-5]|[0-4][0-9])|1[0-9][0-9]|[1-9]?[0-9])|[a-z0-9-]*[a-z0-9]:(?:[\x01-\x08\x0b\x0c\x0e-\x1f\x21-\x5a\x53-\x7f]|\\[\x01-\x09\x0b\x0c\x0e-\x7f])+)\])')
  AS IS_VALID
FROM 
(
  VALUES
  'abc123@mail.com'
, 'abc@mail'  
, 'abc.def@mail#.com'
, 'abc-@mail.com'
) table_info (CM_EMAIL)
CM_EMAIL IS_VALID
abc123@mail.com true
abc@mail false
abc.def@mail#.com false
abc-@mail.com true
Mark Barinstein0
Related