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 executingselect 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.
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)
UPDATE users SET email = REPLACE( email, SUBSTRING_INDEX( email, '@', -1 ) , CONCAT(user_id, 'domain.com' ) ) WHERE [MYSQL CONDITION];
To update live email address to test email address
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.)
UPDATE contacts SET email = REPLACE(email, SUBSTRING_INDEX(email, '@', -1), 'domain.com')
You could try using INSTR
together with SUBSTR
or LEFT
to get the part before the "@" symbol, perhaps.
Something like SELECT LEFT("foo@yaho.com",INSTR("foo@yaho.com","@")-1);
seems to work.
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.
精彩评论