How to Use the REPLACE() Function in MySQL

08/05/2021

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

#MySQL