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

Question

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