Find and replace text in an entire table using MySQL?

Text can be found and replaced with the help of replace() function. its explanation is With the help of the following steps -

First, create a table with the help of create command as shown below -

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

After creating the above table, insert the records with the help of insert command. 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)

With the help of select statement all the records can be displayed as shown below-

mysql> SELECT * from FindAndReplaceDemo;

The following is the output obtained

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

Now, with the help of substitution function , the name Carol was replaced with Taylor. The syntax is Given below -

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

The query using the above syntax is 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. it's a 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 from the above output, Carol is replaced with Taylor.

The above is the detailed content of Find and replace text in an entire table using MySQL?. For more information, please follow other related articles on the PHP Chinese website!