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

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)

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