How can I use MySQL replace() to replace strings in multiple records?

The replace() function can be used to replace a string with another string. To understand replace(), we need to create a table with some records. The following is the query to create a table.

mysql> create table replaceDemo
   -> (
   -> Name varchar(200)
   -> );
Query OK, 0 rows affected (0.55 sec)

Insert some records with the help of INSERT command. The query to insert records is as follows −

mysql> insert into replaceDemo values('John');
Query OK, 1 row affected (0.17 sec)

mysql> insert into replaceDemo values('Demo');
Query OK, 1 row affected (0.16 sec)

mysql> insert into replaceDemo values('Smith');
Query OK, 1 row affected (0.13 sec)

mysql> insert into replaceDemo values('Demo');
Query OK, 1 row affected (0.14 sec)

mysql> insert into replaceDemo values('Carol');
Query OK, 1 row affected (0.14 sec)

Let us check the records present in the table ‘replaceDemo’. The query is as follows.

mysql> select *from replaceDemo;

The following is an example.

+-------+
| Name  |
+-------+
| John  |
| Demo  |
| Smith |
| Demo  |
| Carol |
+-------+
5 rows in set (0.00 sec)

Above, I have Demo value twice in my table and I want to replace “Demo” with the name ‘Amit’. For that, we can use the replace() function from MySQL. The query is as follows −

mysql> UPDATE replaceDemo
   -> SET Name = REPLACE (Name, 'Demo', 'Amit')
   -> WHERE Name LIKE 'Demo';
Query OK, 2 rows affected (0.18 sec)
Rows matched: 2  Changed: 2  Warnings: 0

To check whether the name “Demo” has been replaced with the name ‘Amit’ or not, the following is the query.

mysql> select *from replaceDemo;

Here is the output.

+-------+
| Name  |
+-------+
| John  |
| Ramit |
| Smith |
| Amit |
| Carol |
+-------+
5 rows in set (0.00 sec)

As you can see in the above output, I have changed the name “Demo” with “Amit”.