How to Use the REPLACE() Function in MySQL

Contents
In this article, you will learn how to use the REPLACE() function in MySQL.
Using the REPLACE() function in MySQL
The REPLACE() function in MySQL is used to replace all occurrences of a given substring with a new substring in a string. It is a very useful function when dealing with data manipulation and cleaning tasks.
Syntax
REPLACE(str, old_substring, new_substring)
The function takes three arguments:
- str: The original string that you want to replace the substring in.
- old_substring: The substring that you want to replace.
- new_substring: The new substring that will replace the old substring.
Examples
Here are some examples of how to use the REPLACE() function in MySQL:
Replace a substring in a string
SELECT REPLACE('Hello World!', 'World', 'Universe');
Output: “Hello Universe!”
Replace multiple substrings in a string
SELECT REPLACE(REPLACE('Hello World!', 'Hello', 'Hi'), 'World', 'Universe');
Output: “Hi Universe!”
Replace a substring in a column of a table
UPDATE users SET email = REPLACE(email, '@gmail.com', '@yahoo.com');
This SQL statement updates the email column of the users table, replacing all instances of “@gmail.com” with “@yahoo.com”.
Replace a substring in a column of a table based on a condition
UPDATE users SET email = REPLACE(email, '@gmail.com', '@yahoo.com') WHERE id = 1;
This SQL statement updates the email column of the users table for the user with ID 1, replacing all instances of “@gmail.com” with “@yahoo.com”.
Replace a substring using a subquery
UPDATE users SET email = REPLACE(email, '@gmail.com', '@yahoo.com') WHERE email IN (SELECT email FROM users WHERE status = 'inactive');
This SQL statement updates the email column of the users table for all inactive users, replacing all instances of “@gmail.com” with “@yahoo.com”.