MySQL REPLACE() function
REPLACE() function
MySQL REPLACE() replaces all the occurrences of a substring within a string.
This function is useful in -
- Substring replacement: It allows us to replace all occurrences of a substring with a new substring.
- Removing characters: By replacing a specific substring with an empty string, REPLACE() can remove characters or substrings from a string.
Syntax:
REPLACE(str, find_string, replace_with)
Arguments:
Name | Description |
---|---|
str | A string. |
find_string | A string which is present one or more times within the string str. |
replace_with | A string which will replace every time it finds find_string within str. |
Syntax Diagram:

MySQL Version: 8.0
Example of MySQL REPLACE() function
The following MySQL statement replaces every time it finds ‘ur’ within the ‘w3resource’ by ‘r’.
Code:
SELECT REPLACE('w3resource','ur','r');
Output:
mysql> SELECT REPLACE('w3resource','ur','r'); +--------------------------------+ | REPLACE('w3resource','ur','r') | +--------------------------------+ | w3resorce | +--------------------------------+ 1 row in set (0.02 sec)
Pictorial Presentation:
Example of MySQL REPLACE() function with where clause
The following MySQL statement replaces all the occurrences of ‘K’ with 'SA' within the column country from the table publisher for those rows, in which the column value of country is the UK.
Code:
SELECT pub_city,country,
REPLACE(country,'K','SA')
FROM publisher
WHERE country='UK';
Sample table: publisher
Output:
mysql> SELECT pub_city,country, -> REPLACE(country,'K','SA') -> FROM publisher -> WHERE country='UK'; +-----------+---------+---------------------------+ | pub_city | country | REPLACE(country,'K','SA') | +-----------+---------+---------------------------+ | London | UK | USA | | Cambridge | UK | USA | +-----------+---------+---------------------------+ 2 rows in set (0.05 sec)
MySQL: Find and Replace Data
We have a table called test with following records :
mysql> SELECT * FROM test;
+-----------+ | test_char | +-----------+ | Abcd | | Wxyz | | Scott | | Robin | +-----------+ 4 rows in set (0.00 sec)
To find and replace 'Scott' with 'Sidhu' you can use the following MySQL statement :
mysql> UPDATE test set test_char = replace(test_char, 'Scott', 'Sidhu'); Query OK, 1 row affected (0.04 sec) Rows matched: 4 Changed: 1 Warnings: 0 mysql> SELECT * FROM test;
+-----------+ | test_char | +-----------+ | Abcd | | Wxyz | | Sidhu | | Robin | +-----------+ 4 rows in set (0.00 sec)
Video Presentation:
- Weekly Trends
- Python Interview Questions and Answers: Comprehensive Guide
- Scala Exercises, Practice, Solution
- Kotlin Exercises practice with solution
- MongoDB Exercises, Practice, Solution
- SQL Exercises, Practice, Solution - JOINS
- Java Basic Programming Exercises
- SQL Subqueries
- Adventureworks Database Exercises
- C# Sharp Basic Exercises
- SQL COUNT() with distinct
- JavaScript String Exercises
- JavaScript HTML Form Validation
- Java Collection Exercises
- SQL COUNT() function
- SQL Inner Join