memory lain…

HowTo perform a mySQL search and replace

Here are the steps required to perform a mySQL search and replace. This allows for very fast updating of database fields.

First, login as root and enter mysql

[root@e-smith /root]# mysql

Welcome to the MySQL monitor. Commands end with ; or \g.
Your MySQL connection id is 2 to server version: 3.23.32

Type ‘help;’ or ‘\h’ for help. Type ‘\c’ to clear the buffer

Next load the database you wish to use, syntax is:
use database_name;

mysql> use mydatabase;
Reading table information for completion of table and column names
You can turn off this feature to get a quicker startup with -A

Database changed

Finally run the command line below to perform the search and replace, syntax is:
update tablename set field = replace(field,’search_for_this’,'replace_with_this’);

mysql> update table_name set field = replace(field,’(',”);
Query OK, 0 rows affected (0.08 sec)
Rows matched: 450 Changed: 387 Warnings: 0

When completed, exit mysql

mysql> exit
Example:

Let’s say you have a database named address_book with a table named contact_list and a field name phone_number. Some of your phone numbers were entered as (areacode) number and you wish to remove the () from areacode. Here is what you would do:

[root@e-smith /root]# mysql

Welcome to the MySQL monitor. Commands end with ; or \g.
Your MySQL connection id is 2 to server version: 3.23.32

Type ‘help;’ or ‘\h’ for help. Type ‘\c’ to clear the buffer

mysql> use address_book;
Reading table information for completion of table and column names
You can turn off this feature to get a quicker startup with -A

Database changed

mysql> update contact_list set phone_number = replace(phone_number,’(',”);
Query OK, 0 rows affected (0.08 sec)
Rows matched: 450 Changed: 387 Warnings: 0

mysql> update contact_list set phone_number = replace(phone_number,’)',”);
Query OK, 0 rows affected (0.08 sec)
Rows matched: 450 Changed: 387 Warnings: 0

mysql> exit

Trackback URL