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}$’

  • Share post

A collection of particles named Tali Luvhengo

5 comments

  • anban says:

    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}$’
  • anban says:

    That’s a very good point!
    Thanks 😀

  • 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.

Leave a Reply to anban Cancel reply

Your email address will not be published. Required fields are marked *

Page optimized by WP Minify WordPress Plugin