MySQL query to delete a specific email address, all follow a similar pattern

Question

I wonder if anyone can help me write a MySQL query. I noticed that in my email database I have a huge number of users who have passed my automatic verification of entries that I want to note. They all have the form [e-mail], where abcdefare random names of variable length, then a three-digit number.

I have a field in my table under the name fld_badthat I want to change to 1 in the query.

So something like

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

Obviously, this .....is where my knowledge fails!

Answer 1

you can use mysql regexp command for 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

:

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