Mysql Finding invalid email addresses using SELECT


MYSQL Regular Expressions!! Learn Them! HERE!!!! DO IT NOW!!!

SELECT * FROM users WHERE email NOT REGEXP ‘^[a-zA-Z0-9][a-zA-Z0-9._-]*[a-zA-Z0-9]@[a-zA-Z0-9][a-zA-Z0-9._-]*[a-zA-Z0-9]\.[a-zA-Z]{2,4}$’

About the author

Talifhani Luvhengo

A collection of particles named Tali Luvhengo


  • quick question: why would you have invalid email addresses stored in your DB in any case? You would probably want to make sure that anything saving emails to your db would validate it first and then insert/update? :O

    • Of cause you would validate data before writing to the DB. But some times you get a client(I won’t mention any names), that comes to you and says hey since you’re doing my new site you might as well migrate all my old users to the new site right? And i say of course, why not. And you get emailed the db with 3000 users, and 2800 invalid email address. Argh.

      And then you start looking through the code for comments hoping the old developer left his home address and where his kids go to school if you catch my drift. Only he doesn’t even comment his code. Cause he is a Douche.

      So that’s what you would use that query for. To right the wrongs of this world. So if you want delete them all

      DELETE FROM users WHERE email NOT REGEXP ‘^[a-zA-Z0-9][a-zA-Z0-9._-]*[a-zA-Z0-9]@[a-zA-Z0-9][a-zA-Z0-9._-]*[a-zA-Z0-9]\.[a-zA-Z]{2,4}$’
  • Brilliant, thank you very much. I have exactly that – a client of a client who didn’t validate before adding to their DB. Great to be able to give them a spreadsheet of all the invalid customer information.


Page optimized by WP Minify WordPress Plugin