How to Use REPLACE Function in MySQL: Syntax and Examples

In MySQL, use the REPLACE() function to replace all occurrences of a substring within a string with another substring. The syntax is REPLACE(original_string, from_substring, to_substring), which returns a new string with replacements.

Syntax

The REPLACE() function takes three arguments:

• original_string: The string where you want to replace text.
• from_substring: The substring you want to find and replace.
• to_substring: The substring to replace from_substring with.

The function returns a new string with all occurrences of from_substring replaced by to_substring.

sql

REPLACE(original_string, from_substring, to_substring)

Example

This example shows how to replace all occurrences of 'cat' with 'dog' in a string.

sql

SELECT REPLACE('The cat sat on the cat mat.', 'cat', 'dog') AS replaced_text;

Output

replaced_text
The dog sat on the dog mat.

Common Pitfalls

Common mistakes when using REPLACE() include:

• Using it on NULL values returns NULL, so ensure your input string is not NULL.
• It replaces all occurrences, so partial replacements might happen unintentionally.
• It is case-sensitive, so 'Cat' and 'cat' are treated differently.

sql

/* Wrong: expecting case-insensitive replacement */
SELECT REPLACE('Cat and cat', 'cat', 'dog') AS result_wrong;

/* Right: use LOWER() to handle case-insensitive replacement */
SELECT REPLACE(LOWER('Cat and cat'), 'cat', 'dog') AS result_right;

Output

result_wrong
Cat and dog

result_right
dog and dog

Quick Reference

Parameter	Description
original_string	The string to search within
from_substring	The substring to find and replace
to_substring	The substring to replace with

Key Takeaways

Use REPLACE(original_string, from_substring, to_substring) to substitute text in strings.

REPLACE is case-sensitive and replaces all occurrences of the target substring.

Applying REPLACE on NULL returns NULL, so check for NULL values first.

For case-insensitive replacements, combine REPLACE with LOWER or UPPER functions.

REPLACE returns a new string; it does not modify the original data in tables unless used in UPDATE.