0

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}$')
Rory McCrossan
  • 331,213
  • 40
  • 305
  • 339
Md. Abdul Hannan
  • 3
  • 3
  • What is the input? What is the expected output and the actual output? – data_henrik Sep 15 '22 at 09:46
  • We expect only valid email addresses will show. – Md. Abdul Hannan Sep 15 '22 at 09:56
  • Please edit your question with sample email addresses you tested with. Else we cannot recreate and answer. – data_henrik Sep 15 '22 at 10:12
  • Are your sure that your input data is not padded by some spaces or other characters? – data_henrik Sep 15 '22 at 10:15
  • Your regex has leading ^ and trailing $, which may be unncessarily restrictive for your table-data (your question lacks datatype of the cm_email column, or any sample data). Compare the behaviour without both of these characters , and then adjust your quiery appropriately. – mao Sep 15 '22 at 10:23

1 Answers1

0

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 Barinstein
  • 11,456
  • 2
  • 8
  • 16