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:

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:

All String Functions (Slides presentation)

Previous: REPEAT
Next: REVERSE