FInd invalid email address

Question

How to find invalid email address in a row with SELECT ?

The **only** way to determine email address validity with 100% certainty is to send mail to it. — Charles, Apr 28 '11 at 08:20
What programming language are you using to query the database? Also, what you mean invalid? That doesn't exist? Invalid syntax? — Oscar Mederos, Apr 28 '11 at 08:21
What is I mean is to select rows in table which are not valid email address such fooo@com foo.bar.com etc. — georgevich, Apr 28 '11 at 08:28
@Starx, I won't answer, I'm about to go to sleep. I just hate seeing valid questions downvoted for no good reason. — jcomeau_ictx, Apr 28 '11 at 08:29
@georgevich, fooo@com is technically valid, though unlikely. — jcomeau_ictx, Apr 28 '11 at 08:32
@georgevich: Define ***exactly*** how you want to validate an email. It's the only way you will get a proper answer, there is a lot of (warranted) disagreement on how to properly validate an email address. — Wesley Murch, Apr 28 '11 at 08:35
@duedl0r define invalid? : not respecting the format as specified in RFC 822 and later — sarah.ferguson, Jan 27 '16 at 11:19
@Charles The only way to determine email address validity with 100% certainty is to send mail to it: wrong, he's asking for valid address not "active" or "existing"... — sarah.ferguson, Jan 27 '16 at 11:20

score 17 · Accepted Answer · edited Feb 14 '17 at 08:00

17

SELECT
    email
FROM
    TABLE
WHERE
    email NOT LIKE '%_@_%._%'
AND email NOT LIKE '%list of invalid char%';

edited Feb 14 '17 at 08:00

Faisal

answered Apr 28 '11 at 08:20

Gaurav

3

!@#$%^&*.asdfsdafasd is not a valid email address – Michael Koper Apr 28 '11 at 08:22
@Michael you are correct, but it's a step in the right direction. – jcomeau_ictx Apr 28 '11 at 08:24
Everything after the AND part doesn't make sense, first of all I think you meant email LIKE and second doesn't the invalid characters each need their own clause. – Neo Feb 20 '14 at 01:07

score -1 · Answer 2 · answered Apr 28 '11 at 08:21

-1

SELECT email FROM tablename WHERE email NOT REGEXP '(^[a-z0-9]+)\@(.*)'

answered Apr 28 '11 at 08:21

lobster1234

-1 `local.part_can+be-more%complex@example.com` disagrees with you. See the infamous (but mostly correct) regex at ex-parrot: http://www.ex-parrot.com/pdw/Mail-RFC822-Address.html – Piskvor left the building Apr 28 '11 at 08:23
Too strict in some ways, too lax in others. – Ignacio Vazquez-Abrams Apr 28 '11 at 08:23
1

maybe something like this? `(^([^@\s]+)@((?:[-_a-z0-9]+\.)+[a-z]{2,})$)|(^$)` – Michael Koper Apr 28 '11 at 08:27
That match even valid email address – georgevich Apr 28 '11 at 08:29
@Michael Koper: `info@правительство.рф` disagrees (and it would not work in punycode either: `info@xn--80aealotwbjpid2k.xn--p1ai`) – Piskvor left the building Apr 28 '11 at 08:29
I guess try the regexes from here then? http://www.regular-expressions.info/email.html – lobster1234 Apr 28 '11 at 08:32
@lobster1234: I guess that's outdated (no IDN); plus the author says "I know it's too strict and rejects valid addresses, but I just don't care". – Piskvor left the building Apr 28 '11 at 08:33
3

omg, it doesn't make sense to argue about the correct regex. everybody knows there is not an easy solution to this. just accept the theoretical way how to handle it with mysql and find a suitable regex for your needs. – duedl0r Apr 28 '11 at 08:35

2 Answers2