MySQL provides a useful string function called Replace that allows you to replace a string in a column of a table by another. In this article, we will discuss the MySQL Replace Function which can replace all occurrences of a sub-string by a new sub-string in a string. Also, we will discuss a few examples to show its usage.
Syntax
REPLACE(string, old_string, new_string)
Parameters
The MySQL Replace Function has three parameters. The description of the parameters is as follows:
- string: The first parameter is the string in which you want to replace a sub-string. Also, it can be the name of the column of the table.
- old_string: The second parameter is a valid string which the function will search in the string. It will be replaced by the string in the third parameter.
- new_string: The third parameter is the value of the string that you want to replace in place of the old string.
Return Value
The Replace Function in MySQL returns the original string with new_string replacing the old_string in it. However, this function does not support regular expressions so you cannot replace a string based on a pattern.

Examples
Let’s discuss a few examples to demonstrate the usage of function.
Example 1: SELECT Query
For instance, consider a basic example to use the MySQL Replace in a SELECT Query.
mysql> SELECT REPLACE ('I love Dogs. Dogs are cute.', 'Dogs', 'Cats'); +---------------------------------------------------------+ | REPLACE ('I love Dogs. Dogs are cute.', 'Dogs', 'Cats') | +---------------------------------------------------------+ | I love Cats. Cats are cute. | +---------------------------------------------------------+ 1 row in set (0.00 sec)
In the above example, we replace the sub-string ‘Dogs’ with ‘Cats’ in a string. You can observe function replaces all occurrences of the old_string with the new value.
Sample Table
For the following examples, consider the given sample table for reference.
mysql> select * from user_description; +-----------+---------------------------------------------------+ | user_name | user_description | +-----------+---------------------------------------------------+ | Chandler | Chandler Muriel Bing is a fictional character. | | Monica | Monica E. Geller is a fictional character. | | Ross | Ross Eustace Geller, Ph.D., is a fictional charact| +-----------+---------------------------------------------------+
Example 2: SELECT Query With Column
In this example, we will apply the MySQL Replace Function on a column of the sample table.
mysql> select user_name, REPLACE(user_description, 'fictional', 'real') from user_description; +-----------+---------------------------------------------------+ | user_name | user_description | +-----------+---------------------------------------------------+ | Chandler | Chandler Muriel Bing is a real character. | | Monica | Monica E. Geller is a real character. | | Ross | Ross Eustace Geller, Ph.D., is a real charact. | +-----------+---------------------------------------------------+
We replace the sub-string fictional with a new sub-string real while selecting the user_description column. You can observe that the MySQL Replace Function replaces the occurrences of the old string in all the rows.
Example 3: UPDATE Query
Similarly, we can also the function in an update query to replace all occurrences of a string.
mysql> UPDATE user_description SET user_description = REPLACE(user_description, 'fictional', 'real'); Query OK, 3 rows affected (0.04 sec) Rows matched: 3 Changed: 3 Warnings: 0 mysql> SELECT * from user_description; +-----------+---------------------------------------------------+ | user_name | user_description | +-----------+---------------------------------------------------+ | Chandler | Chandler Muriel Bing is a real character. | | Monica | Monica E. Geller is a real character. | | Ross | Ross Eustace Geller, Ph.D., is a real charact. | +-----------+---------------------------------------------------+
As you can observe in the above example, the function updates all the rows of the table replacing occurrences of fictional with real in all the rows.
Conclusion
In conclusion, we discussed the MySQL Replace Function in this article. It is very helpful in replacing a sub-string with another in a string. You can learn more about it on the Official MySQL Documentation.
Also, you can learn more about MySQL on Concatly!

Vishesh is currently working as an Intermediate Software Engineer with Orion Health, New Zealand. He graduated with a Masters in Information Technology from the University of Auckland in 2021. With more than 4 years of work experience, his expertise includes Java, Python, Machine Learning, PHP, Databases, Design and Architecture.