Find and Replace Text in Entire Table Using MySQL

The text can be found and replaced with the help of the replace() function. It is explained with the help of the following steps −

First, a table is created with the help of the create command which is given as follows −

mysql> CREATE table FindAndReplaceDemo
-> (
-> FirstName varchar(200)
-> );
Query OK, 0 rows affected (0.43 sec)

After creating the above table, the records are inserted with the help of the insert command. This is given below −

mysql> INSERT into FindAndReplaceDemo values('john');
Query OK, 1 row affected (0.15 sec)

mysql> INSERT into FindAndReplaceDemo values('smith');
Query OK, 1 row affected (0.17 sec)

mysql> INSERT into FindAndReplaceDemo values('Bob');
Query OK, 1 row affected (0.12 sec)

mysql> INSERT into FindAndReplaceDemo values('carol');
Query OK, 1 row affected (0.18 sec)

All the records can be displayed with the help of the select statement which is given as follows −

mysql> SELECT * from FindAndReplaceDemo;

The following is the output obtaine

+-----------+
| FirstName |
+-----------+
| john      |
| smith     |
| Bob       |
| carol     |
+-----------+
4 rows in set (0.00 sec)

Now, the name Carol is replaced with Taylor with the help of the replace function. The syntax for that is given below −

UPDATE yourTableName SET column_name= replace(column_name, 'Old_Value', 'New_Value');

The query using the above syntax is given as follows −

mysql> UPDATE FindAndReplaceDemo SET FirstName = replace(FirstName, 'carol', 'Taylor');
Query OK, 1 row affected (0.14 sec)
Rows matched: 4 Changed: 1 Warnings: 0

The contents of the table can be viewed again with the help of the SELECT statement. This is given below −

mysql> SELECT * from FindAndReplaceDemo;

The following is the output obtained

+-----------+
| FirstName |
+-----------+
| john      |
| smith     |
| Bob       |
| Taylor    |
+-----------+
4 rows in set (0.00 sec)

As can be seen with the above output, Carol is replaced with Taylor.