How can MySQL find and replace the data with REPLACE() function to UPDATE the table?

MySQLMySQLi Database

As we know that REPLACE () function is used to replace the occurrences of a substring with another substring within a string. We can also use REPLACE function with UPDATE statement to update the table by finding and replacing the data.

Example

mysql> Update Student set Father_Name = REPLACE(Father_Name, 'Mr.','Shri ');
Query OK, 5 rows affected (0.06 sec)
Rows matched: 5 Changed: 5 Warnings: 0

mysql> Select Name, Father_Name from Student;
+---------+-----------------+
| Name    | Father_Name     |
+---------+-----------------+
| Gaurav  | Shri Ramesh     |
| Aarav   | Shri Sanjay     |
| Harshit | Shri Lovkesh    |
| Gaurav  | Shri Ramchander |
| Yashraj | Shri Mohan      |
+---------+-----------------+
5 rows in set (0.00 sec)

The above query has updated the column Father_name by finding ‘Mr.’ and replacing the same with ‘Shri’.

raja
Published on 20-Feb-2018 15:49:33
Advertisements