MYSQL Email Validation with Regex not working

Question

enter image description here

i just want to fetch the invalid email addresses from my database, i tried with the following query, but its not working

$sql=mysql_query("SELECT * FROM mytable WHERE email!='' and email NOT REGEXP '^[A-Z0-9._%-]+@[A-Z0-9.-]+\.[A-Z]{2,4}$'");

And the invalid Email is a.bcdefg-3@abccom

What happens now, no results returned or an error? Can you provide a bad email you'd expect this to find? — chris85, Jun 02 '15 at 13:25
Please, [stop using `mysql_*` functions](http://stackoverflow.com/questions/12859942/why-shouldnt-i-use-mysql-functions-in-php). They are no longer maintained and are [officially deprecated](https://wiki.php.net/rfc/mysql_deprecation). Learn about [prepared statements](http://en.wikipedia.org/wiki/Prepared_statement) instead, and consider using PDO, [it's not as hard as you think](http://jayblanchard.net/demystifying_php_pdo.html). — Jay Blanchard, Jun 02 '15 at 13:25
its returned no result and the invalid email is a.bcdefg-3@abccom — Ezhil, Jun 02 '15 at 13:26
Maybe use something like that: [a-zA-Z0-9][a-zA-Z0-9\.\_\-]+@[a-zA-Z0-9](?:[a-zA-Z0-9]+\.[a-zA-Z]{2,4})+ — ventaquil, Jun 02 '15 at 13:37

glefait · Accepted Answer · 2018-09-28T19:31:52.633

3

It looks like the Richard answer is correct however, it may not works given the collation used. Therefore, if you have a case sensitive collation, you may want to lowercase your field.

Try this query :

SELECT * FROM `mytable` WHERE `email` NOT REGEXP '^[[:alnum:]._%-\+]+@[[:alnum:].-]+[.][[:alnum:]]{2,4}$';

I have updated the regex to use character classes instead of character range to avoid lower (or upper) case transformation.

Moreover, in some IDE, you may have to escape "." with two backslashes, therefore I use

[.]

instead of escaped dot.

I updated again to allow subdomains. Edited to allow +, thanks to @Charlie Brumbaugh comment.

edited Sep 28 '18 at 19:31

answered Jun 02 '15 at 15:52

glefait

1,651
1
13
11

its working for the email a.bcdefg-3@abccom but it also fetching the valid email address [email protected] – Ezhil Jun 03 '15 at 09:51
[email protected] is a valid email address. If you really need to test the email domain/subdomain validity, don't do it through mysql. – glefait Jun 03 '15 at 09:52
i just want to fetch the invalid email address but it fetching the valid email address also – Ezhil Jun 03 '15 at 09:53
You're correct, check the last edit, it should work ! – glefait Jun 03 '15 at 09:56
thanks man..finally its working..can you please tell me why mine is not working – Ezhil Jun 03 '15 at 10:04
Your regex was invalid at least because of the case. Moreover, the dot escaping may be wrong with your editor, that's why I chose to use [.] instead of \. – glefait Jun 03 '15 at 10:10
Thanks a lot, i had the same problem and was looking for a solution for 2 hours. – Waqas Ibrahim Jun 30 '16 at 13:19
This doesn't let users have the plus sign + in their emails. An adjusted regex would be REGEXP '^[[:alnum:]._%-\+]+@[[:alnum:].-]+[.][[:alnum:]]{2,4}$' – Charlie Brumbaugh Sep 27 '18 at 22:54
thanks, @CharlieBrumbaugh, I've updated the regex given your comment. – glefait Sep 28 '18 at 19:32
So now it doesn't let users have - in the their emails, which is valid so long as its not the first character an updated one is ^[[:alnum:]._%-\+]+[[:alnum:]._%-\+-]+@[[:alnum:].-]+[.][[:alnum:]]{2,4}$ – Charlie Brumbaugh Oct 11 '18 at 20:37

Richard · Answer 2 · 2015-06-02T14:13:43.390

0

Try this:

SELECT * FROM `mytable` WHERE `email` NOT REGEXP '^[A-Z0-9._%-]+@[A-Z0-9.-]+\.[A-Z]{2,4}$';

Works for my.

Furthermore, do not use the MySQL driver as it is deprecated in PHP.

edited Jun 02 '15 at 14:13

answered Jun 02 '15 at 14:02

Richard

2,840
3
25
37

Is it working in phpMyAdmin/Adminer? Are you sure that there is an invalid entry? – Richard Jun 02 '15 at 14:06
its not working in phpMyAdmin and pls find the screenshot of the row – Ezhil Jun 02 '15 at 14:11
@Ezhil: Try the edited RegEx. You should validate the email address at input time and it should not be allowed to insert a invalid email address in database. This is not consistent. – Richard Jun 02 '15 at 14:14

MYSQL Email Validation with Regex not working

2 Answers2