邮件列表问题

Question

My sql query to get all the e-mail addresses from my table is as follows:

SELECT DISTINCT cEmail
  FROM tblUsers
  WHERE cEmail IS NOT NULL AND
        cEmail LIKE '%_@__%.__%'

However, when I insert the list to gmail, it's still saying there are invalid e-mails. There are over 2000 e-mails and hard to go through each one to find out the problem.

Anyway to improve my query?

Answer 1

You may check using regulars expressions with MySQL unsing the REGEXP operator See : http://dev.mysql.com/doc/refman/5.0/en/regexp.html#operator_regexp

And find / adapt a decent email checking regular expression (Google "regexp email") Example : \b[A-Z0-9._%-]+@[A-Z0-9.-]+\.[A-Z]{2,4}\b found here : http://www.regular-expressions.info/regexbuddy/email.html