How to find email with more than 2 dots using REGEXP MySQL function?

Question

I have a big user database with a lot of spammy email account, like [email protected] or [email protected] and I want to remove them with one queries (instead of doing a php script that parses all the database).

I see MySQL have a REGEXP function, but it's hard to use it with "dot". I tried lots of things like :

• SELECT * FROM users WHERE email REGEXP '([[...]]){2,}'
• SELECT * FROM users WHERE email REGEXP '(\\.){2,}'
• SELECT * FROM users WHERE email REGEXP '(.*)[[...]]{2}(.*)'

Each one fails.

Any ideas ?

Answer 1

how about this?

SELECT * FROM users WHERE email REGEXP '[.].*[.].*@';

this searches for two (or more) dots before @ (which would still allow multi-dot domain names like .co.uk etc).

However, you should consider the possibility that some users may really have multiple dots in their username as well, and that will still be legit.

Answer 2

As far as I've seen, this is usually only a spam issue when a gmail.com account is used. Most legitimate usernames with multiple dots will be something like [email protected] - so spammers who use 3 or more dots in their username - I will be pruning from my database, and I will be implementing code to the registration process to disallow the use of more than 2 dots in the username when a gmail address is used.

SELECT user_email FROM users WHERE email REGEXP '[.].*[.].*[.].*@';