MySQL REPLACE function: Syntax, examples, and safe updates

What is the MySQL REPLACE function?

MySQL REPLACE is a tool for string manipulation that replaces all occurrences of a specified substring with another substring.

This function is useful in many scenarios where you need to modify data that matches a specific pattern, such as correcting dates, fixing transposed letters or numbers, updating obsolete values, etc. REPLACE searches a string for every occurrence of the specified substring and replaces it with the new value you provide.

MySQL REPLACE quick syntax

The basic MySQL REPLACE syntax is as follows.

REPLACE(original_text, text_to_find, new_text)

The function accepts the following parameters:

• original_text: The original string you want to modify, such as a word or sentence
• text_to_find: The substring you want to find and replace
• new_text: The substring that will replace text_to_find

Here is a simple example. You have a products table where some product names contain 'OldModel', and you want to replace it with 'NewModel'. You can use REPLACE as follows.

UPDATE products
SET product_name = REPLACE(product_name, 'OldModel', 'NewModel');

This statement searches the product_name column for every occurrence of 'OldModel' and replaces it with 'NewModel'.

MySQL REPLACE vs REPLACE INTO

REPLACE and REPLACE INTO have similar names, which can cause confusion. However, they serve completely different purposes in MySQL.

REPLACE is a string function. It searches a string for all occurrences of a specified substring and replaces them with another substring. By itself, REPLACE does not modify the data stored in a table. To save the changes, you need to use it within a data modification statement such as UPDATE.

REPLACE INTO is a data modification statement. It inserts a new row or replaces an existing one in a table, and it is especially helpful if the new row conflicts with an existing row based on a PRIMARY KEY or UNIQUE index. MySQL deletes the existing row and inserts the new one. If there is no conflict, it simply inserts the new row.

The following table summarizes the key differences between REPLACE and REPLACE INTO.

Use cases for the MySQL REPLACE function

The REPLACE function can save a lot of time and effort in many scenarios. Let's explore some of the most common use cases where it can be particularly helpful.

Throughout this article, we'll demonstrate these use cases with dbForge Studio for MySQL, an AI-powered IDE for MySQL and MariaDB databases, as well as related cloud platforms. dbForge Studio for MySQL offers a full set of features to cover the entire database lifecycle, from SQL code writing to database administration and version control, with rich visualization and automation capabilities and dedicated AI assistance on SQL development.

Cleansing data and correcting typos

Data may contain incorrect characters, typos, or formatting inconsistencies that need to be corrected. REPLACE provides a quick way to fix such issues.

Here is an example: an email domain has been misspelled as @sakilacastomer.org. Instead of finding and correcting each occurrence manually, you can use the REPLACE command.

SELECT
  customer_id,
  email AS original_email,
  REPLACE(email, '@sakilacastomer.org', '@sakilacustomer.org') AS corrected_email
FROM customer
WHERE email LIKE '%@sakilacastomer.org';

Note that this statement does not modify the stored data. It only returns the transformed value in the REPLACE MySQL query results. After that, you can apply the UPDATE statement.

UPDATE customer
SET email = REPLACE(email, '@sakilacastomer.org', '@sakilacustomer.org')
WHERE email LIKE '%@sakilacastomer.org';

It is a good practice to run a SELECT statement first to verify that you are targeting the correct data and that the resulting values are as expected.

Removing unwanted characters

Extra symbols, spaces, and other unwanted characters can make data inconsistent. You can use REPLACE to remove them quickly. For example, to remove asterisks (*) from a string, replace them with an empty string.

SELECT
  customer_id,
  last_name AS original_last_name,
  REPLACE(last_name, '*', '') AS corrected_last_name
FROM active_customers
WHERE last_name LIKE '%*%';

Hiding sensitive data

REPLACE can also help modify data used for testing or reporting when sensitive information needs to be hidden. For example, the following statement replaces each 'a' in the user's last name with an asterisk.

UPDATE active_customers
SET last_name = REPLACE(last_name, 'A', '*');

Replacing text in specific columns

Another common use case is replacing specific text in a particular column. For example, you can replace the abbreviation St. with Street in the address column.

UPDATE employees
SET address = REPLACE(address, 'St.', 'Street')
WHERE address LIKE '%St.%';

Using MySQL REPLACE in a column replaces every occurrence of the abbreviation St. within the values in the address column.

Using REPLACE in a MySQL view

A view is a stored query that presents data in a specific format without modifying the underlying table. Using REPLACE in a view allows you to transform or format text visually and leave the original data unchanged.

Let us take one more example for Sakila: the film table has a special_features column, which stores comma-separated values (e.g., Trailers, Deleted Scenes, and Behind the Scenes). Let us convert the data in this column into a more readable, bulleted format. We create a view that will show the original values alongside the formatted values. This way, we can see how it would look in the table and leave everything underlying untouched.

CREATE VIEW vw_film_special_features_formatted
AS
SELECT
  film_id,
  title,
  special_features AS original_special_features,
  REPLACE(special_features, ',', ' • ') AS formatted_special_features
FROM film;

REPLACE is simple and convenient, making it an attractive option whenever you need to modify text quickly. However, it requires care and caution when using it to avoid unintended results. In the next section, we'll examine the risks and how to avoid them.

Special considerations when using MySQL REPLACE

There are several issues you should keep in mind to avoid incorrect results or unexpected behavior. In particular, you should understand how the REPLACE function handles case sensitivity, NULL values, and duplicate or repeated values.

Case sensitivity

The MySQL REPLACE function is case-sensitive. In one of our previous examples, we replaced a letter in the customers' last names with an asterisk. But how will it work if we don't specify the case?

Let us replace all occurrences of 'r' in customers' last names with asterisks.

SELECT
  customer_id,
  last_name AS original_last_name,
  REPLACE(last_name, 'r', '*') AS modified_last_name
FROM active_customers
WHERE last_name LIKE '%r%';

Since Sakila's last names are stored in all uppercase (e.g., ANDERSON), no matches will be found because there are no lowercase letters to replace. You need to specify 'R' as the value to be replaced explicitly.

SELECT
  customer_id,
  last_name AS original_last_name,
  REPLACE(last_name, 'R', '*') AS modified_last_name
FROM active_customers
WHERE last_name LIKE '%r%';

Replacement of NULL values

Another important consideration is how REPLACE handles NULL values. If any of its arguments—the source string, the substring to find, or the replacement string—is NULL, the function returns NULL.

SELECT
  film_id,
  title,
  original_language_id,
  REPLACE(original_language_id, NULL, 1) AS replace_attempt
FROM film_test;

If your data contains NULL values, you can use IFNULL() to change them to empty strings before applying REPLACE.

SELECT
  film_id,
  title,
  original_language_id,
  IFNULL(original_language_id, 1) AS original_language_id_fixed
FROM film_test;

Now you can actually update the data and replace NULLs with the correct value.

UPDATE film_test
SET original_language_id = IFNULL(original_language_id, 1);

This way, you can replace a string in MySQL even if it contains NULLs. However, this approach converts NULL values to empty strings in the query result, which may not always be desired.

Handling of duplicate and repeated values

This function replaces all occurrences of the specified substring within a string, not just the first one. You should consider it when the same substring appears more than once.

For example, we want to replace the word 'Boring' with 'Action-Packed' in the film descriptions. However, 'Boring' occurs multiple times in some descriptions, so REPLACE replaces every occurrence.

SELECT
  description,
  REPLACE(description, 'Boring', 'Action-Packed') AS description_updated
FROM film_text_copy_test
WHERE description LIKE '%Boring%';

How to use MySQL REPLACE with UPDATE safely

Using REPLACE with UPDATE modifies stored data, so it is important to use this combination carefully. If you define the conditions incorrectly, UPDATE can modify more data than intended. This operation may be difficult to reverse.

Two simple precautions make the task safer:

1. Preview changes before running UPDATE
2. Use a WHERE clause to limit the rows

Preview changes before running the UPDATE command

In the examples above, we ran the SELECT statement with the REPLACE expression before modifying data with UPDATE. It shows you which rows are affected and what the new values will look like. This practice is useful not only with REPLACE but also with all operations that actually modify stored data.

Add a WHERE clause to limit affected rows

Use a WHERE clause to restrict the update to rows that actually contain the target substring. Without an appropriate condition, the UPDATE statement may process more rows than necessary.

The basic pattern is as follows.

UPDATE table_name
SET column_name = REPLACE(column_name, 'old', 'new')
WHERE column_name LIKE '%old%';

Whenever possible, add more conditions to narrow the scope of the operation. Before making large or important changes, preview the results with a SELECT statement, test the operation in a non-production environment, and make a backup or establish a recovery option.

MySQL REPLACE vs other string functions

REPLACE is one of several MySQL functions for manipulating strings. Depending on the task, other functions may be more appropriate or can be combined with REPLACE for more precise manipulations. These include SUBSTRING(), CONCAT(), and REGEXP_REPLACE.

Common MySQL REPLACE mistakes and fixes

MySQL REPLACE is a powerful function that should be used with care. Improper use can produce unexpected results, incorrect behavior, or even data loss.

Below are the most common issues you may encounter when working with MySQL REPLACE and recommendations for troubleshooting and resolving them.

Using MySQL REPLACE in dbForge Studio for MySQL

dbForge Studio for MySQL is one of the most user-friendly AI-powered IDEs for MySQL and MariaDB databases. It offers a comprehensive toolset for all database tasks, including AI-assisted SQL development, database management and version control, administration, and extensive automation.

When working with MySQL REPLACE(), dbForge Studio for MySQL provides a range of coding assistance features. Its SQL Editor offers intelligent code completion, syntax validation, debugging, code formatting, and a library of code snippets to help you accelerate SQL development, reduce errors, and make the overall process more efficient.

Throughout this article, we have used dbForge Studio for MySQL to demonstrate REPLACE() use cases, write and execute queries against a test database, and inspect the output in the results grid. However, the Studio offers additional features that can further simplify SQL development.

The integrated AI Assistant can generate SQL queries from natural-language prompts, as well as analyze, troubleshoot, and optimize existing queries.

When working with MySQL REPLACE(), for example, you can describe what you want to accomplish: "Open the active_customers table and change the category value from Platinum to Platinum VIP for customers who paid more than 4000. Preview the changes before updating the data." The AI Assistant can then generate the appropriate query for you.

Download dbForge Studio for MySQL to write and test MySQL queries under your real workload.

Conclusion

MySQL REPLACE is a powerful and helpful function that lets you correct errors or replace outdated information in a string using just one command.

We have covered how REPLACE works, how to use it with other SQL commands like UPDATE and SELECT, and how to make sure that its usage is safe and correct. Now you can try it out properly with dbForge Studio for MySQL and see how it can make your data management more straightforward and efficient.

FAQ