MySQL Replace Function | Replace Substring in a String

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!