Search code examples
mysqlemailemail-address

Mysql-how to update the "domain.com" in "address@domain.com"

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.

Solution

  • 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 ('test1@yahoo.com');
INSERT INTO users VALUES ('test2@yaho.com');
INSERT INTO users VALUES ('test3@yahoo.com');


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           |
+-----------------+
| test1@yahoo.com |
| test2@yahoo.com |
| test3@yahoo.com |
+-----------------+
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 ('TEST4@YAHOO.COM');


SELECT * FROM users;   
+-----------------+
| email           |
+-----------------+
| test1@yahoo.com |
| test2@yahoo.com |
| test3@yahoo.com |
| TEST4@YAHOO.COM |
+-----------------+
4 rows in set (0.00 sec)


SELECT * FROM users WHERE email COLLATE latin1_general_cs = UPPER(email);
+-----------------+
| email           |
+-----------------+
| TEST4@YAHOO.COM |
+-----------------+
1 row in set (0.00 sec)