MySQL REPLACE Function: Syntax and Examples

If you're managing data in a MySQL database and need to fix or adjust some text, the REPLACE function makes it easy. The REPLACE() function in MySQL is used to update all occurrences of a substring with another substring in the given string argument.

This function comes in handy when you want to fix a typo, swap a word, or even update a bunch of records with just one command. If you want to replace substrings in text or update multiple database records, REPLACE can save you a ton of manual work.

What is the MySQL REPLACE function?

The REPLACE into MySQL can be summarized in one sentence: it replaces all occurrences of a substring with another substring.

While this is generally true, to explain this statement a little better, it's worth first looking at the structure of MySQL. The database management system uses tables to store data and make it available again when needed. This data is stored in the form of strings enclosed in single or double quotes. However, it can happen that a typo creeps in or that stored information becomes obsolete. This is where MySQL REPLACE comes in.

Instead of laboriously searching for every occurrence of the superfluous substring and running the risk of overlooking a variant, MySQL REPLACE does this task for you. The command searches for all occurrences of the stored substring in a string and replaces it with new information that you define. This allows you to replace an outdated URL, fix transposed numbers or letters, or update other changed values ​​in your table. The result is an optimized and updated data set. The MySQL REPLACE command is very easy to use and has few potential pitfalls.

The basic format of the function looks this way:

                   
REPLACE(original_text, text_to_find, new_text)
                        

The parameters are:

• original_text: The full sentence or word you're working with
• text_to_find: The bit you want to change
• new_text: What you want to use instead

Syntax of the MySQL REPLACE function

The MySQL FIND REPLACE function consists of three different parameters separated by commas. Their purpose becomes clear when you look at the syntax.

The basic syntax looks this way: REPLACE(original_text, text_to_find, new_text)

1. original_text

   This is the full text you're working with. It can be something you type in directly (like a sentence), or it can come from a column in your database.

   Example: 'I love tea'

2. text_to_find

   This is the exact word or bit of text you want to change. MySQL will look for this.

   Example: 'tea'

3. new_text

   This is what you want to use instead.

   Example: 'coffee'

Now let's put it all together in a simple example:

                   
 SELECT REPLACE('I love tea', 'tea', 'coffee');
                        

What you get back: I love coffee

For instance, you have a table of products and some of the names say “OldModel” and you want to change them to “NewModel.”

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

This tells MySQL: “Hey, go through the product_name column and swap out 'OldModel' for 'NewModel' wherever you find it.”

Use cases of MySQL REPLACE function

The REPLACE function in MySQL is super handy with the real-world data. REPLACE can save you hours of manual editing in different cases: if you're cleaning up messy entries, fixing typos, or swapping out sensitive information.

Data cleansing – Sometimes your data has weird characters or outdated formats. REPLACE makes it easy to fix them.

For example, replace slashes / with dashes - in date fields saved as text:

                   
SELECT REPLACE('2025/04/22', '/', '-');
-- Result: '2025-04-22'
                        

Correcting typos – Typos happen. Instead of hunting them down one by one, use REPLACE.

For example, fixing a misspelled email domain:

                   
UPDATE users
SET email = REPLACE(email, '@gmial.com', '@gmail.com');
                        

Removing unwanted characters - Extra symbols, spaces, or weird formatting can sneak into your data. REPLACE can get rid of them fast.

For example, remove asterisks * from text:

                   
SELECT REPLACE('Best*Seller*Product', '*', '');
-- Result: 'BestSellerProduct'  
                        

Replacing sensitive data – If you're sharing your database for testing or reports, you might want to hide real names or details.

For example, replace user names with “Anonymous”:

                   
UPDATE users
SET name = REPLACE(name, name, 'Anonymous'); 
                        

Also, the MySQL search replace process is made simple with the REPLACE function, allowing you to quickly find and update specific text within your database records.

Replacing text in specific columns

If you want to update only one column in a table (like fixing all product names or company names) REPLACE makes it super easy.

For example, updating product model names:

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

This will change every product_name that contains “OldModel” to say “NewModel” instead.

Replacing multiple occurrences of a substring

REPLACE doesn't just change the first match — it replaces every match in the string.

For example, let's say someone entered the same word twice:

                   
SELECT REPLACE('This is is a test', 'is', 'was');
-- Result: 'Thwas was was a test'
                        

See? Both "is" instances as well as part of the word were swapped to "was".

Another example is cleaning up extra periods in sentences:

                   
SELECT REPLACE('Hello... How are you...', '...', '.');
-- Result: 'Hello. How are you.'
                        

REPLACE is one of those simple tools that quietly does a lot of work behind the scenes. Once you start using it, you'll wonder how you ever managed without it.

Special considerations when using MySQL REPLACE

The MySQL FIND and REPLACE function is super handy when you need to clean up or fix text in your database. But there are a few things to be aware of to avoid surprises. Let's go over a few important points, like how REPLACE handles uppercase vs. lowercase letters, what happens when there's missing data (NULL), and how it handles duplicates.

Case sensitivity in MySQL REPLACE

It's important to remember that MySQL REPLACE is case-sensitive. That means it sees "Hello" and "hello" as two different things. This also means that MySQL REPLACE won't produce the desired result if you don't pay attention to the case when writing the command.

For example,

                   
SELECT REPLACE('Hello World', 'hello', 'Hi');
-- Result: 'Hello World' (no change, because 'hello' ≠ 'Hello')
                        

So if you want to replace text without worrying about upper or lower case, you'll need a little trick.

How to do case-insensitive replacement:

Use LOWER() (or UPPER()) to turn everything into the same case first:

                   
SELECT REPLACE(LOWER('Hello World'), 'hello', 'hi');
-- Result: 'hi world'
                        

Just note: this changes the whole text to lowercase. If you want to keep the original formatting, you might need a more advanced solution (like using MySQL REGEXP REPLACE 8.0+).

Replacing NULL values with MySQL REPLACE

This one's important: REPLACE doesn't work on NULL values. If any of the inputs (the text, the thing to find, or the thing to replace it with) is NULL, the result will also be NULL.

For example,

                   
SELECT REPLACE(NULL, 'a', 'b');
-- Result: NULL 
                        

So if there's any chance your data might have NULLs, you should handle that first.

Use the IFNULL() function to turn NULLs into empty strings before using REPLACE:

                   
SELECT REPLACE(IFNULL(your_column, ''), 'old', 'new') FROM your_table;
                        

This way, REPLACE won't break when it hits a NULL value.

Some users confuse the REPLACE() function (for strings) with the REPLACE INTO statement (which is used to insert or update rows in a table). Keep in mind: they're two completely different things!

Also, when replacing text, make sure you're not accidentally changing something you didn't mean to (especially if the substring appears in multiple places or overlaps with other values).

Combining MySQL REPLACE with other SQL functions

The MySQL string replace function is ideal for modifying text by swapping out specific words or characters within a string. The REPLACE function becomes way more powerful when you combine it with other SQL commands like SELECT, UPDATE, and even INSERT. This lets you search for and replace text not just in a single string, but across multiple records.

Think of it as a team effort: REPLACE handles the text, and the other SQL commands help you decide where and how to use it.

Using REPLACE with UPDATE statements

Let's say you want to update a bunch of rows in a table, like fixing a company name or swapping out an outdated product label. REPLACE + UPDATE makes this easy.

For example,

                   
UPDATE customers
SET company_name = REPLACE(company_name, 'Ltd.', 'Inc.')
WHERE company_name LIKE '%Ltd.%';
                        

This goes through every customer with “Ltd.” in their company name and replaces it with “Inc.” — only in the rows that match.

This is perfect for fixing typos or making changes across many rows at once.

Using REPLACE in a MySQL view

A view is like a saved query. It doesn't change your actual table but shows data the way you want to see it. You can use REPLACE inside a view to clean or format your data on the fly, without permanently changing anything in your database.

For example,

                   
CREATE VIEW cleaned_products AS
SELECT 
    product_id,
    REPLACE(product_name, 'OldBrand', 'NewBrand') AS updated_name
FROM products; 
                        

It creates a view called cleaned_products where all instances of "OldBrand" in product names are shown as "NewBrand"—but your actual products table stays untouched.

This is handy when you want to display updated text without actually editing your data.

MySQL REPLACE vs other string functions

Find and Replace MySQL has a bunch of functions that help you work with text. While REPLACE is great for swapping out parts of a MySQL replace string, it's not the only tool in the box. Let's compare it with a few other popular string functions: SUBSTRING, CONCAT, and REGEXP_REPLACE.

REPLACE – Finds a specific piece of text and replaces it with something else.

It is best for quick fixes like correcting typos, changing brand names, removing unwanted characters. You can use MySQL REPLACE multiple times in one query to handle tasks like MySQL REPLACE multiple characters in a string efficiently.

For example,

                   
SELECT REPLACE('Hello world', 'world', 'everyone');
-- Result: 'Hello everyone' 
                        

SUBSTRING – Pulls out part of a string, based on position.

It is best when you need just a piece of a string, like the first few characters of a product code or email username.

For example,

                   
SELECT SUBSTRING('product_12345', 9);
-- Result: '12345'
                        

It doesn't change the string, it just returns a portion of it.

CONCAT – Joins (or “concatenates”) two or more strings together.

It is best for combining values like first and last names, or building a sentence from different columns.

For example,

                   
SELECT CONCAT('Hello, ', 'John!');
-- Result: 'Hello, John!'
                        

Use it when you're building new text, not fixing old text.

REGEXP_REPLACE (MySQL 8.0+) – Replaces parts of a string using regular expressions (a fancy way to match patterns).

If you need to perform advanced text substitutions in your database, the MySQL REGEXP_REPLACE function is perfect for replacing patterns using regular expressions. It is best for more complex MySQL update replacements, like swapping numbers, matching optional characters, or dealing with inconsistent formatting.

For example,

                   
SELECT REGEXP_REPLACE('Call me at 123-456-7890', '[0-9]', 'X');
-- Result: 'Call me at XXX-XXX-XXXX'
                        

Use REGEXP_REPLACE when simple REPLACE isn't powerful enough.

By the way, there are other databases. While MySQL exists for using structured tables and functions like REPLACE, databases like MongoDB take a more flexible approach to storing and updating data.

Enhance your MySQL development with dbForge Studio for MySQL

dbForge Studio for MySQL is a great tool for managing MySQL databases. It helps you build, manage, and optimize your databases.

Features like SQL code suggestions, easy data editing, and visual query building make your work faster and easier. It's a complete toolkit for MySQL development.

Try dbForge Studio for MySQL today with a free trial and see how it can help you work smarter with automated management, powerful data tools, and simple query creation!

Conclusion

The MySQL REPLACE function is a powerful tool. With the MySQL REPLACE command, you can correct errors or replace outdated information in a string. It's important to pay attention to case sensitivity.

We covered how REPLACE works, how to use it with other SQL commands like UPDATE and SELECT, and how it compares to other MySQL Replace part of string functions like SUBSTRING, CONCAT, and REGEXP_REPLACE. It's a versatile function that belongs in every SQL user's toolkit.

Now that you've learned how REPLACE works, try it out in your own database! Use the examples we've shared and see how it can make managing your data quicker and easier.

Want to go further? Download a free trial of our database management tool and explore even more powerful features like REPLACE — all in one simple, user-friendly interface. It's a great way to take your SQL skills to the next level!

FAQ