The World of MySQL & MariaDB

MySQL REPLACE Function Tutorial for Efficient String Substitution

Functions
目次

1. Introduction

Overview

In database administration it is often necessary to replace parts of stored text. For example, when a product name changes or an address update is required you will frequently need to overwrite an existing substring with a new one. By using the MySQL REPLACE function you can perform these substitutions efficiently. This article explains the basic to the advanced usage of the REPLACE function in detail.

Purpose of this article

The goal is to learn the fundamentals of the MySQL REPLACE function and to use it in practical database operations. Through this article you will understand how to use REPLACE from simple single substitutions to multiple replacements.

2. Basic usage of the REPLACE function

Syntax and explanation

REPLACE replaces a specific substring in a given string with a new string. The syntax is:

REPLACE(str, from_str, to_str)
  • str : The original string to operate on.
  • from_str : The substring you want to replace.
  • to_str : The new string to replace with.

All occurrences of from_str inside str will be replaced by to_str. Note that the replacement is case‑sensitive.

Basic example

For example, if you want to replace “Java” with “JAVA” in “Java and JavaScript is good”:

SELECT REPLACE('Java and JavaScript is good', 'Java', 'JAVA');

The result is “JAVA and JavaScript is good”. REPLACE searches for from_str across the entire string and replaces it with to_str.

Case‑sensitive behavior

REPLACE is case‑sensitive so “Java” and “java” are treated as different. The example below replaces only “AaA”.

SELECT REPLACE('aaa AaA aAa aaA', 'AaA', 'REPLACE');

Only “AaA” is replaced. Understanding this is important when using REPLACE.

3. Practical example: replacing a single string

Example 1: simple string replacement

If you want to replace “old product” with “new product” inside a product name:

SELECT REPLACE('これは旧製品です', '旧製品', '新製品');

The result becomes “This is a new product.” REPLACE replaces all matches of from_str inside the target string.

Example 2: replacing multibyte characters

REPLACE also supports multibyte text such as Japanese. Example:

SELECT REPLACE('ここは港区です', '港区', '中央区');

The result becomes “Thisuo Ward.” REPLACE works correctly with multibyte characters.

4. How to replace multiple strings at the same time

Nesting REPLACE

You can nest REPLACE calls. Example converting Japanese numerals “一 二 三” to “1 2 3”:

UPDATE t_test SET emp = REPLACE(REPLACE(REPLACE(emp, '一', '1'), '二', '2'), '三', '3');

Nesting is convenient but readability drops if depth increases. For complex mappings consider other approaches.

Using CASE expression

For conditional replacement use CASE. This can improve readability.

UPDATE t_test SET emp = CASE 
    WHEN emp LIKE '%一' THEN REPLACE(emp,'一','1')
    WHEN emp LIKE '%二' THEN REPLACE(emp,'二','2')
    WHEN emp LIKE '%三' THEN REPLACE(emp,'三','3')
    ELSE emp
END;

CASE is helpful when replacing based on specific conditions.

5. Performance and best practices

Performance impact

REPLACE on a large dataset may take time, especially when modifying many rows. Consider the following:

  • Indexes : Use indexes to speed up lookups.
  • Batch execution : Split the task into multiple batches when handling large data.

Optimal usage

Follow these practices to safely operate data:

  • Backups : Always back up before replacements.
  • Test runs : Validate on a test environment first.
  • WHERE clause : Limit target rows with WHERE .

6. Notes and common errors

Case sensitivity

REPLACE is case‑sensitive so you may get unexpected results. To replace both “Java” and “java” convert text to lowercase or uppercase first using LOWER or UPPER.

Combining with other functions

You can combine REPLACE with other string functions. For example with CONCAT or SUBSTRING. Always confirm behavior before running a query.

Common errors and troubleshooting

Typical errors include the target not found or unintended parts being replaced. Confirm the target data beforehand and always test before production.

7. Summary

REPLACE is powerful for MySQL string operations. From basic usage to multi‑target replacement it improves database maintenance efficiency. Pay attention to case‑sensitivity, performance impact, and interaction with other functions.

By applying REPLACE properly you can keep your data consistent. Apply the techniques introduced here to improve your MySQL operations.

8. Related information

Other string functions

Other string functions that work well with REPLACE:

  • CONCAT : Concatenates multiple strings.
  • SUBSTRING : Extracts a part of a string.
  • TRIM : Removes extra whitespace from start and end.

Links to related articles

Useful reference articles:

Use these resources to further improve your MySQL string handling skills.