MySQL query to remove certain email address, all follow similar pattern

Question

Wondered if someone could help me write a MySQL query. I noticed in my email database I have a huge amount of users who got past my automated entry checks who I want to flag. They are all of the form [email protected] where abcdef are random names etc of varible length, then a 3 digit number.

I have a field in my table called fld_bad which I want to change to 1 in the query.

So something like

UPDATE tbl_users SET fld_bad = "1" WHERE fld_email ..... 

Obviously the ..... is where my knowledge is failing me!

Answer 1

you can use the mysql regexp command to do this

http://dev.mysql.com/doc/refman/5.1/en/regexp.html#operator_regexp

UPDATE tbl_users SET fld_bad = "1" WHERE fld_email REGEXP '[A-Za-z]+[0-9]{3}@hotmail\\.com' = 1;

Answer 2

You can use:

UPDATE tbl_users 
SET fld_bad = "1" 
WHERE fld_email REGEXP '[[:alpha:]]+[[:digit]]{3}@hotmail\\.com'