UPDATING partial string from a field mysql

Question

I have a table with columns like:

emails, country_code etc

Some of the rows contain emails like:

[email protected]

I want to change about 10,000 records like this in a way that will affect only the "googlemail.com" part of the value and change all of them to "gmail.com".

UPDATE exmple_table SET emails = REPLACE(emails, '%googlemail.com','%gmail.com');

I tried to find and replace but that making me have to type all 10,000 addresses in the query, any solutions?

Answer 1

You can use 'like' operator to filter out the records which contain 'googlemail' and then perform the string replace on them, as shown below:

update table
set SET emails = REPLACE(emails, 'googlemail.com','gmail.com')
where emails like '%googlemail.com%'