How to Replace Strings in MySQL: REPLACE() and REGEXP_REPLACE() (MySQL 8.0+)

Introduction

When working with MySQL, it’s not uncommon to run into situations like “I want to replace only a specific string across a table” or “I need to fix incorrect data in bulk.” For example, when a URL used in product descriptions on an e‑commerce site changes, when you want to correct past input mistakes all at once, or when you need to standardize formatting (e.g., converting hyphens to slashes), you’ll often need string replacement.

In this article, we thoroughly explain practical methods and techniques for replacing strings in MySQL, from basics to advanced usage. You’ll learn the fundamentals of the commonly used REPLACE() function, along with real‑world examples, efficient ways to perform multiple replacements, and advanced replacement using regular expressions.

We also cover differences in available functions depending on your MySQL version, important precautions to prevent accidental data loss, and performance considerations—providing information that is genuinely useful in real‑world environments.

After reading this article, you will be able to solve all of the following questions and challenges:

• What’s the easiest way to replace a specific string in MySQL?
• How can I replace multiple patterns in bulk?
• Is flexible replacement using regular expressions possible?
• What are the risks and precautions when updating a large amount of data at once?
• How can I back up data to prevent failures?

This content is ideal for everyone from beginners to hands‑on practitioners who want to confidently master string replacement in MySQL.

MySQL String Replacement Basics (REPLACE Function)

If you want to replace strings in MySQL, the most commonly used tool is the REPLACE() function. REPLACE() finds a specific pattern within a given string or column value and replaces it in bulk with another string.
It’s useful in many situations, including routine data fixes and large‑scale batch conversions in databases.

Basic Syntax of REPLACE()

REPLACE(original_string, search_string, replacement_string)

• original_string : The string to modify, or a table column name.
• search_string : The portion you want to replace.
• replacement_string : The new string to insert.

For example:

SELECT REPLACE('I love MySQL!', 'MySQL', 'PostgreSQL');

This query searches for “MySQL” in the string “I love MySQL!” and replaces it with “PostgreSQL,”
so the result becomes “I love PostgreSQL!”.

Case Sensitivity

REPLACE() performs case‑sensitive search and replacement. For example, “mysql” and “MySQL” are treated as different strings.
If the target string is not found, the original string is returned as‑is.

Supported Data Types

REPLACE() can be used with common string columns such as CHAR, VARCHAR, and TEXT. However, be careful with special data types such as BLOB, as behavior may not be as expected.

In this way, REPLACE() is attractive because it is simple and intuitive to use.
In the next sections, we’ll explain practical SQL examples using REPLACE() and how to apply it to table data.

Basic Usage and Practical Examples

While REPLACE() is very simple, in real‑world work you often need to “replace strings in a database column in bulk.” Here, we’ll explain basic operations with REPLACE() and how to batch‑fix table data using concrete SQL examples.

Simple String Replacement with SELECT

First, here is the most basic usage: replacing text in a specific string.

SELECT REPLACE('Hello, mysql user!', 'mysql', 'MySQL');

This query replaces “mysql” with “MySQL,” returning “Hello, MySQL user!”.

Bulk Replacement in a Table Column with UPDATE

A common real-world use case is replacing strings across a specific column in a table.
For example, if you want to update all occurrences of the old domain oldsite.com to the new domain newsite.com in product descriptions, you can run the following SQL:

UPDATE products
SET description = REPLACE(description, 'oldsite.com', 'newsite.com');

This SQL replaces every occurrence of “oldsite.com” in the description column of the products table with “newsite.com”.

Precautions When Executing

An UPDATE using REPLACE() is executed against all records, which means there is a risk of rewriting more than intended.
Before running it in production, always take a backup and verify behavior in a test environment.

Limiting the Impact with a WHERE Clause

If you only want to fix a subset of data, use a WHERE clause. For example, to target only products added in 2024 or later:

UPDATE products
SET description = REPLACE(description, 'oldsite.com', 'newsite.com')
WHERE created_at >= '2024-01-01';

This helps prevent unnecessary rewrites.

Once you understand these basics, everyday operations and data‑cleaning tasks become much more efficient.

Common Use‑Case Sample Collection

REPLACE() is helpful in many real‑world situations where you think, “Can I replace this?” Below are practical samples for common use cases.

1. Fixing Typos and Mistyped Text

For example, it’s useful when you want to correct frequent input mistakes in bulk.

UPDATE users
SET profile = REPLACE(profile, 'htto://', 'http://');

This SQL batch‑corrects the mistakenly entered “htto://” to “http://”.

2. Bulk Replacement of URLs or Domains

This is useful when you redesign or migrate a website and want to replace an old domain with a new one across your data.

UPDATE blog_posts
SET content = REPLACE(content, 'old-domain.jp', 'new-domain.jp');

3. Removing Unwanted Spaces, Newlines, or Symbols

If unintended spaces or newline codes are mixed into your data, you can remove them in bulk with REPLACE().

UPDATE addresses
SET zipcode = REPLACE(zipcode, ' ', '');

This example removes all spaces from postal codes. For newlines, specify '\n' or '\r'.

4. Standardizing Formats ( Hyphen to Slash, Full‑Width to Half‑Width, etc. )

You can also standardize data formats easily with REPLACE().

UPDATE products
SET code = REPLACE(code, '-', '/');

If you want to convert full‑width characters to half‑width in bulk, you can also nest REPLACE() multiple times.

5. Replacing Multiple Patterns at Once

If you want to replace multiple patterns at the same time, nest REPLACE() calls.

UPDATE contacts
SET note = REPLACE(REPLACE(note, '株式会社', '（株）'), '有限会社', '（有）');

This converts “株式会社” and “有限会社” into abbreviated forms in one go.

REPLACE() is a powerful feature for efficiently handling tasks that require “lots of edits” and would be painful to do manually.

Advanced Techniques and Avoiding Troubles

REPLACE() is very convenient, but depending on how you use it, you may run into unexpected trouble—or you may be able to operate much more efficiently. Here we explain practical advanced techniques and tips to prevent problems.

1. Handling NULL Values

If the target column is NULL, REPLACE() returns NULL as well. This can leave data unchanged in unexpected ways. If you want to ensure replacement is applied even when NULL values exist, combine it with IFNULL().

UPDATE users
SET comment = REPLACE(IFNULL(comment, ''), 'NGワード', '***');

This treats NULL as an empty string so replacement will be applied.

2. Case‑Insensitive Replacement

REPLACE() is case‑sensitive by default. If you want to replace both uppercase and lowercase variations, it’s common to combine LOWER() / UPPER() for comparison and perform replacement in two passes as needed.

UPDATE articles
SET title = REPLACE(REPLACE(title, 'MySQL', 'MariaDB'), 'mysql', 'MariaDB');

3. Multi-Step Replacement (Nested REPLACE)

If you want to replace multiple different patterns at once, nest REPLACE() calls.

UPDATE logs
SET message = REPLACE(REPLACE(message, 'error', 'warning'), 'fail', 'caution');

4. Limit the Impact with UPDATE + WHERE

Instead of updating all data at once, use a WHERE clause to target only the rows you need.

UPDATE customers
SET email = REPLACE(email, '@oldmail.com', '@newmail.com')
WHERE registered_at >= '2023-01-01';

5. Always Verify in a Test Environment and Take Backups

Updates using REPLACE() are often difficult to undo. Before running them in production, always take a backup. Testing thoroughly with sample data or in a staging environment greatly reduces risk.

By applying REPLACE() correctly, you can perform string replacement tasks more safely and efficiently.

String Replacement with Regular Expressions (MySQL 8.0+ Only)

In MySQL 8.0 and later, you can use not only REPLACE() but also REGEXP_REPLACE() to perform advanced replacement using regular expressions. This enables flexible pattern matching and efficient data cleansing for complex cases.

Basic Syntax of REGEXP_REPLACE()

REGEXP_REPLACE(original_string, regex_pattern, replacement_string)

• original_string : The string or column name to modify.
• regex_pattern : The pattern to match (e.g., [0-9]{3}-[0-9]{4} ).
• replacement_string : The new string to insert.

Example 1: Removing Hyphens from Phone Numbers

If you want to remove all hyphens from phone numbers, you can write:

UPDATE users
SET tel = REGEXP_REPLACE(tel, '-', '');

Example 2: Standardizing Postal Code Format

Regular expressions are also useful to standardize postal codes in different formats (e.g., “123-4567” and “1234567”).

UPDATE addresses
SET zipcode = REGEXP_REPLACE(zipcode, '([0-9]{3})-?([0-9]{4})', '\1-\2');

This SQL standardizes both “1234567” and “123-4567” into the “123-4567” format.

Example 3: Removing Non-Alphanumeric Characters

You can also remove all characters other than letters and digits.

UPDATE records
SET code = REGEXP_REPLACE(code, '[^a-zA-Z0-9]', '');

This removes all non-alphanumeric characters from the code column.

How to Check Your MySQL Version

REGEXP_REPLACE() is available only in MySQL 8.0 and later. You can check your current MySQL version with this query:

SELECT VERSION();

If you are using an older version such as MySQL 5.x, REGEXP_REPLACE() is not available, so consider using REPLACE() or performing replacement on the application side.

Regex-based replacement is extremely powerful when data patterns vary widely or when complex transformations are required.

Comparison with Other String Functions and Notes

MySQL provides several useful string functions. Since each function has different purposes and characteristics, it’s important to choose the best one for replacement and editing tasks. Here we compare common functions such as REPLACE(), REGEXP_REPLACE(), INSERT(), and CONCAT().

1. REPLACE

• Use case : Replace an “exact match” substring within a string or column with another string.
• Characteristics : Case-sensitive; easiest for simple replacement.
• Example :

  SELECT REPLACE('cat and dog', 'cat', 'fox');
  -- → "fox and dog"
  

2. REGEXP_REPLACE (MySQL 8.0+)

• Use case : Replace parts that match a regular expression pattern.
• Characteristics : Great for complex pattern matching, replacing multiple patterns, and partial extraction/editing.
• Example :

  SELECT REGEXP_REPLACE('a123b456c', '[a-z]', '');
  -- → "123456"
  

3. INSERT

• Use case : “Insert” a string by overwriting a specified length starting at a specified position.
• Characteristics : Suitable for partial replacement/insertion, but more like overwrite than typical replacement.
• Example :

  SELECT INSERT('abcdef', 2, 3, 'XYZ');
  -- → "aXYZef"
  

4. CONCAT

• Use case : “Concatenate” multiple strings or column values.
• Characteristics : Not for replacement/editing; used to join strings together.
• Example :

  SELECT CONCAT('abc', '123');
  -- → "abc123"
  

5. SUBSTRING / LEFT / RIGHT

• Use case : Extract part of a string.
• Characteristics : Ideal for slicing and extracting parts of data.
• Example :

  SELECT SUBSTRING('abcdef', 2, 3);
  -- → "bcd"
  

Quick Comparison Table

By choosing the right function based on your use case and MySQL version, you can operate on data more efficiently and safely.

Performance and Precautions

When performing bulk string replacement in MySQL, especially on large tables or in production, you may encounter unexpected issues or performance degradation. Here are key precautions and performance tips for doing the work safely and efficiently.

1. Be Careful with Bulk Updates on Large Datasets

UPDATE statements using REPLACE() or REGEXP_REPLACE() scan and rewrite the target rows. With large datasets, execution time increases and server load can become significant. On tables with tens of thousands to millions of rows, other queries may slow down, and in worst cases locks or timeouts can occur.

2. Impact on Indexes

If an UPDATE changes values in indexed columns (e.g., email, code), indexes may need to be rebuilt. This can affect performance. To avoid unnecessary rewrites, it’s important to narrow the target data with a WHERE clause.

3. Use Transactions and Rollback

For large rewrites, using a transaction allows you to roll back if an error occurs or if replacement results are not as expected.

START TRANSACTION;
UPDATE users SET comment = REPLACE(comment, 'A', 'B') WHERE ...;
-- If everything looks good
COMMIT;
-- If something goes wrong
ROLLBACK;

This approach helps you work more confidently.

4. Always Back Up in Production

Before performing large‑scale batch updates, always take a backup first. If unexpected corruption or data loss occurs, you can restore from the backup.

5. Batch Processing or Split Execution Can Help

If the number of rows is extremely large, instead of updating everything at once, consider splitting the work into chunks (e.g., by ID ranges), or running it during off‑peak hours to reduce server load.

UPDATE logs
SET message = REPLACE(message, 'error', 'info')
WHERE id BETWEEN 1 AND 10000;

Step‑by‑step execution helps distribute server load.

By considering performance and safety, you can achieve both operational efficiency and trouble prevention when performing string replacement.

Case Study: Real‑World Usage Examples

Here are two practical examples of “string replacement” that are useful in real operations. Both commonly occur during routine maintenance and data management. We’ll explain the workflow and precautions along with actual queries.

Case 1: Bulk Updating URLs in Product Descriptions

This case involves an e‑commerce site where, after a site renewal, you want to replace the old URL (old-shop.com) with the new URL (new-shop.jp) across all product descriptions.

Example Steps:

1. Back up the products table before starting
2. Narrow down target rows using a WHERE clause (test runs recommended)
3. Run the UPDATE statement for the bulk fix

Actual SQL Example:

UPDATE products
SET description = REPLACE(description, 'old-shop.com', 'new-shop.jp');

Precautions:

• Always back up and validate thoroughly in a test environment before applying to production
• If spaces or newlines may surround the URL, consider regex replacement (REGEXP_REPLACE) as well

Case 2: Standardizing Customer Data Formats

String replacement is also useful for standardizing formats such as phone numbers and postal codes in customer databases. For example, to remove hyphens from all phone numbers and make them continuous digits:

Actual SQL Example:

UPDATE customers
SET tel = REPLACE(tel, '-', '');

If you are on MySQL 8.0 or later, you can also use regex for more flexible formatting fixes.

Regex Example (Standardize postal codes to “123-4567”):

UPDATE customers
SET zipcode = REGEXP_REPLACE(zipcode, '([0-9]{3})-?([0-9]{4})', '\1-\2');

Precautions:

• Validate that results are as expected before applying changes to production data
• If the impact is large, narrow down the update scope step-by-step using a WHERE clause

As shown in these case studies, MySQL string replacement is highly useful for large‑scale maintenance and routine data cleaning. By consistently backing up and validating beforehand, you can prevent mistakes and trouble.

Summary and Work Checklist

So far, we’ve covered how to replace strings in MySQL—from basics to advanced techniques and real‑world examples. Finally, let’s summarize the key points and provide a checklist that is useful when performing actual operations.

Key Takeaways

• REPLACE() is best for simple bulk string replacement. It is case‑sensitive and works only on exact matches.
• REGEXP_REPLACE() (MySQL 8.0+) enables advanced pattern replacement using regular expressions.
• Bulk replacement with UPDATE is convenient, but backups and validation in a test environment are essential .
• Nesting REPLACE() is effective for replacing multiple patterns at once.
• Pay close attention to performance, index impact, and server load when processing large datasets.

Work Checklist

• □ Have you accurately identified the pattern to replace and the target column(s)?
• □ Have you narrowed down updates to only necessary rows using a WHERE clause?
• □ Have you taken a backup before applying changes to production?
• □ Have you validated the query behavior using a test environment or dummy data?
• □ Do you have a plan to use batch processing or LIMIT/ID ranges depending on data volume and server load?
• □ After the operation, have you thoroughly verified that the replacement results match expectations?
• □ Have you chosen the appropriate function based on your MySQL version?

By following this checklist, you can perform MySQL string replacement safely and reliably.
Because even a small mistake can lead to major issues, be sure to verify each step carefully before applying changes to production.

FAQ (Frequently Asked Questions)

Here we summarize common questions and real‑world concerns about “MySQL string replacement.” Use this section to reduce uncertainty and work more confidently.

Q1. Is REPLACE() case‑sensitive?

A1: Yes. REPLACE() is case‑sensitive. For example, “mysql” and “MySQL” are treated as different strings. If you need to replace both, you can nest REPLACE() twice or use another approach.

Q2. What happens if I use it on a column that contains NULL?

A2: If the target column is NULL, the result of REPLACE() is also NULL, and nothing changes. If you want to replace even when NULL values exist, combine it with IFNULL().

Q3. Can I replace multiple patterns at once?

A3: Yes. You can nest REPLACE() calls to replace multiple patterns in one statement. However, results may differ depending on replacement order, so validate beforehand.

Q4. How can I replace using regular expressions?

A4: Use REGEXP_REPLACE(), which is available in MySQL 8.0 and later. If you are using an older version, consider application-side replacement or another method.

Q5. What is the difference from REPLACE INTO?

A5: REPLACE() is a function for “string replacement,” while REPLACE INTO is an INSERT-like SQL statement that “deletes an existing row and inserts a new row.” They serve completely different purposes.

Q6. Can I restore the original data after replacement? (Recovery)

A6: In most cases, it’s difficult to revert data after replacement. Always take a backup before you run the operation. If something goes wrong, restore from your backup.

Q7. How can I check my MySQL version?

A7: Run the following query to check your MySQL version:

SELECT VERSION();

Check your version in advance to confirm whether functions like REGEXP_REPLACE() are available.

Use these FAQs to proceed with string replacement operations confidently.

Related Links and References

If you want to go deeper into string replacement or SQL learning, using official documentation and reliable technical resources is effective. We also introduce related articles and useful tools—please use them as references.

1. Official MySQL Documentation

• MySQL :: MySQL 8.0 Reference Manual :: 12.8.2 String Functions A comprehensive list of MySQL string functions. You can confirm detailed specifications and arguments for REPLACE() and REGEXP_REPLACE() here.

2. Practical Examples of REGEXP_REPLACE()

• Studying the regex functions added in MySQL 8 Includes many practical samples of regex-based replacement introduced in MySQL 8.0.

4. If You Want to Learn SQL Basics

• MySQL Basics (Beginner) – Dotinstall If you want to learn SQL from the ground up, video-based learning resources can also be helpful.