How is the replace function in MySQL used?

6 months ago

1 minute

The REPLACE function in MySQL is used to replace a part of text within a string. Its syntax is as follows:

REPLACE(str, search_str, replace_str)

In this case, ‘str’ represents the string that needs to be replaced, ‘search_str’ is the text to be replaced, and ‘replace_str’ is the new text used for the replacement.

Here is a simple example:

Suppose there is a table named “customers” that contains the following information:

+----+----------+---------------------+
| id | name     | email               |
+----+----------+---------------------+
| 1  | John     | john@example.com    |
| 2  | Steve    | steve@example.com   |
| 3  | Mary     | mary@example.com    |
+----+----------+---------------------+

We want to replace “.com” with “.net” in the email. You can use the following SQL statement:

UPDATE customers
SET email = REPLACE(email, '.com', '.net');

After execution, the data in the table will change to:

+----+----------+---------------------+
| id | name     | email               |
+----+----------+---------------------+
| 1  | John     | john@example.net    |
| 2  | Steve    | steve@example.net   |
| 3  | Mary     | mary@example.net    |
+----+----------+---------------------+

In this way, the replacement of the string is completed.