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:

Important Notes:

MySQL Version: 8.0

Example of MySQL REPLACE() function

The following MySQL statement replaces every occurrence of the substring 'ur' with 'r' in the string 'w3resource'.

Code:

-- Select the result of the REPLACE() function
SELECT 
    REPLACE('w3resource', 'ur', 'r'); 
    -- The REPLACE() function replaces all occurrences of 'ur' with 'r' in the string 'w3resource'

Explanation:

• SELECT REPLACE('w3resource','ur','r');


• SELECT: This keyword is used to initiate a query to retrieve data from the database.


• REPLACE: This function is used to replace all occurrences of a specified string within another string.


• 'w3resource': This is the original string from which certain characters will be replaced.


• 'ur': This is the substring that we want to find within the original string.


• 'r': This is the substring that will replace each occurrence of 'ur' found in the original string.

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 if not found:

The following MySQL code attempts to replace all occurrences of the substring 'abcd' with 'not found' within the string 'w3resource'.

Code:

-- This SQL statement replaces a substring within a given string.
SELECT 
    REPLACE('w3resource', 'abcd', 'not found');

Explanation:

• REPLACE('w3resource', 'abcd', 'not found'): This function replaces occurrences of a specified string ('abcd') within another string ('w3resource') with a new string ('not found').


• 'w3resource': This is the original string in which we want to replace a substring.


• 'abcd': This is the substring within the original string that we want to replace.


• 'not found': This is the string that will replace the occurrences of the substring.


• This code essentially looks for the substring 'abcd' within the string 'w3resource'. If it finds any occurrences of 'abcd', it replaces them with 'not found'. However, since 'abcd' does not exist within 'w3resource', the result will simply be the original string 'w3resource' without any changes.

Output:

mysql> SELECT REPLACE('w3resource','abcd','not found');
+------------------------------------------+
| REPLACE('w3resource','abcd','not found') |
+------------------------------------------+
| w3resource                               | 
+------------------------------------------+

Example of MySQL REPLACE() function with where clause

The following MySQL statement replaces all occurrences of 'K' with 'SA' within 'country' column from the table publisher. This transformation is applied only to rows where the original 'country' value is 'UK'.

Sample table: publisher

Code:

-- Select the 'pub_city' and 'country' columns from the 'publisher' table
SELECT 
    pub_city,          -- Select the city where the publisher is located
    country,           -- Select the country where the publisher is located
    REPLACE(country, 'K', 'SA') AS modified_country  -- Replace occurrences of 'K' with 'SA' in the 'country' column and alias it as 'modified_country'
FROM 
    publisher          -- Specify the table to query from, which is 'publisher'
WHERE 
    country = 'UK';    -- Apply the condition to only select rows where the 'country' is 'UK'

Explanation:

• The query retrieves data from the publisher table.


• It selects the following columns:


• pub_city: the city where the publisher is located.


• country: the country where the publisher is located.


• REPLACE(country, 'K', 'SA') AS modified_country: a new column where all occurrences of the letter 'K' in the country column are replaced with 'SA'. This column is aliased as modified_country.


• The WHERE clause filters the rows to include only those where the country is 'UK'.

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: Using REPLACE() with Dynamic Values

Sample table: new_test_1

+------+--------+-------------------------+
|col_id|col_name|col_des                  |
|------+--------+-------------------------+
|     1|row1    |This is the first row.   |
|     2|row2    |This is the second row.  |
|     3|row3    |This is the third row.   |
+------+--------+-------------------------+	 

Suppose we want to replace the word 'second' with '2nd' in the col_des. We can use a REPLACE() statement with dynamic values. The following MySQL statements can be used

Code:

-- Define the dynamic values
SET @target_word = 'second';
SET @replacement_word = '2nd';

-- Use REPLACE() to update the col_des
UPDATE new_test_1
SET col_des = REPLACE(col_des, @target_word, @replacement_word);

Explanation:

• Define Dynamic Values:


• SET @target_word = 'second'; - This sets the word we want to replace.


• SET @replacement_word = '2nd'; - This sets the word that will replace the target word.


• Use REPLACE() in an UPDATE Statement:


• UPDATE new_test_1 SET col_des = REPLACE(col_des, @target_word, @replacement_word); - This updates the col_des column in the new_test_1 table by replacing all occurrences of @target_word with @replacement_word.

If we want verify the update the following MySQL statements can be used

SELECT * FROM new_test_1

Output:

col_id|col_name|col_des               |
------+--------+----------------------+
     1|row1    |This is the first row.|
     2|row2    |This is the 2nd row.  |
     3|row3    |This is the third row.|

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)

Frequently Asked Questions (FAQ) - MySQL Replace Function

1. What is the REPLACE() function in MySQL?

The REPLACE() function in MySQL is used to replace all occurrences of a specified string within another string with a new string.

2. How does the MySQL REPLACE() function work?

The REPLACE() function searches for all instances of a substring within a string and replaces each instance with a new substring.

3. Can MySQL REPLACE() be used with any data type?

The REPLACE() function is generally used with string data types, such as CHAR, VARCHAR, and TEXT.

4. Is the MySQL REPLACE() function case-sensitive?

Yes, the REPLACE() function is case-sensitive. It will only replace occurrences of the exact substring specified, including matching the case.

5. Can MySQL REPLACE() modify the original data in a table?

The REPLACE() function itself does not modify data in a table. It returns the modified string, which can be used in an UPDATE statement to change table data.

6. In what scenarios is the MySQL REPLACE() function useful?

• Substring Replacement: It allows replacing all occurrences of a substring with a new substring.


• Removing Characters: By replacing a specific substring with an empty string, characters or substrings can be removed from a string.

7. How does MySQL REPLACE() handle substrings that are not found in the original string?

If the substring to be replaced is not found in the original string, the REPLACE() function returns the original string unchanged.

8. Can MySQL REPLACE() be used with dynamic values?

Yes, the REPLACE() function can be used with dynamic values, such as column names or variables, in SQL queries.

9. What are the differences between MySQL REPLACE() and other similar functions like UPDATE or INSERT?

REPLACE() is a string function for modifying strings within expressions, while UPDATE and INSERT are data manipulation statements used to modify table data. REPLACE INTO is an alternative to INSERT with different behavior for handling existing rows.

Video Presentation:

All String Functions (Slides presentation)

Previous: MySQL REPEAT ()
Next: MySQL REVERSE ()