SQL find and replace

It's remarkable easy, and quick, to do a search/replace on a MySQL database. PHPmyAdmin makes it even easier.

For example, say you have a table containing Web links to a server on your network and the server name gets changed or, more likely, the Web pages get moved. You might have links such as http://oldwebserver/importantstuff/index.html that should now be http://newwebserver/importantstuff/index.html.  To correct these with an SQL statement, use:

UPDATE table_name SET field_name = REPLACE (
field_name,
"old text",
"new text");

For example:

UPDATE mytable SET url = REPLACE (
url,
"http://oldwebserver/importantstuff/",
"http://newwebserver/importantstuff/");

Note: The reason I didn't just replace "oldwebserver" with "newwebserver" is as a safeguard against changing things I didn't want changed. You've got to be pretty careful about what you do, and I would strongly advise that you always back up the database before making any changes like this.

WordPress expert Lorelle has some good advice about doing this kind of thing on your WordPress tables at:

http://lorelle.wordpress.com/2005/12/01/search-and-replace-in-wordpress-mysql-database/