Mysql-how to update the "domain.com" in "[email protected]"

Question

Hi there,

In my database I have a lot of users who've misspelled their e-mail address. This in turn causes my postfix to bounce a lot of mails when sending the newsletter.
Forms include (but are not limited to) "yaho.com", "yahho .com" etc.
Very annoying!

So i have been trying to update those record to the correct value.
After executing select email from users where email like '%@yaho%' and email not like '%yahoo%'; and getting the list, I'm stuck because I do not know how to update only the yaho part. I need the username to be left intact.

So I thought I would just dump the database and use vim to replace, but I cannot escape the @ symbol..

BTW, how do I select all email addresses written in CAPS? select upper(email) from users; would just transform everything into CAPS, whereas I just needed to find out the already-written-in-CAPS mails.

Answer 1

You could try using INSTR together with SUBSTR or LEFT to get the part before the "@" symbol, perhaps.

Something like SELECT LEFT("[email protected]",INSTR("[email protected]","@")-1); seems to work.

Answer 2

To address your second question (about finding emails written in caps), something like this might be helpful:

select email from users where upper(email) = email

(Forgive me if the syntax is not precisely correct, since I'm used to DB2. The idea is to compare the straight email address with the upper-cased version.)

Answer 3

You may want to try something like the following:

UPDATE   users
SET      email = CONCAT(LEFT(email, INSTR(email, '@')), 'yahoo.com')
WHERE    email LIKE '%@yaho.com%';

Test case:

CREATE TABLE users (email varchar(50));

INSERT INTO users VALUES ('[email protected]');
INSERT INTO users VALUES ('[email protected]');
INSERT INTO users VALUES ('[email protected]');


UPDATE   users
SET      email = CONCAT(LEFT(email, INSTR(email, '@')), 'yahoo.com')
WHERE    email LIKE '%@yaho.com%';

Query OK, 1 row affected (0.00 sec)
Rows matched: 1  Changed: 1  Warnings: 0


SELECT * FROM users;
+-----------------+
| email           |
+-----------------+
| [email protected] |
| [email protected] |
| [email protected] |
+-----------------+
3 rows in set (0.00 sec)

To answer your second question, you probably need to use a case sensitive collation such as the latin1_general_cs:

SELECT * FROM users WHERE email COLLATE latin1_general_cs = UPPER(email);

Test case:

INSERT INTO users VALUES ('[email protected]');


SELECT * FROM users;   
+-----------------+
| email           |
+-----------------+
| [email protected] |
| [email protected] |
| [email protected] |
| [email protected] |
+-----------------+
4 rows in set (0.00 sec)


SELECT * FROM users WHERE email COLLATE latin1_general_cs = UPPER(email);
+-----------------+
| email           |
+-----------------+
| [email protected] |
+-----------------+
1 row in set (0.00 sec)

Answer 4

For the first question, I would choose something like

UPDATE users
SET email = INSERT(email,INSTR(email,'@'), LENGTH(email), '@yahoo.com')
WHERE email LIKE '%@yaho.com'

Just for the sake of being thorough, this is multi-byte safe even though I've used LENGTH. All that's needed is for the third argument of INSERT to be at least as large as the end of the substring.

Syntactic's answer of finding the all-caps email is a good answer. Possibly performing slightly faster, although you would likely not notice the difference, is

SELECT email FROM users WHERE BINARY(email) NOT REGEXP '[a-z]'

Update: BINARY(email) is needed to force case-sensitive matching.