CODEWITHSUNDEEP
db2regexp-like
Md. Abdul Hannan
Md. Abdul Hannan

Reputation: 3

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

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}$')

Upvotes: 0

Views: 461

Answers (1)

Mark Barinstein
Mark Barinstein

Reputation: 12299

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
  '[email protected]'
, 'abc@mail'  
, 'abc.def@mail#.com'
, '[email protected]'
) table_info (CM_EMAIL)
CM_EMAIL IS_VALID
[email protected] true
abc@mail false
abc.def@mail#.com false
[email protected] true

Upvotes: 0

Related Questions