If you need to search for and replace text in a database column, MySQL provides a built in method similar to other scripting languages.

MySQL replace() method

This MySQL statement is an especially useful technique for publishers who need to update snippets of content across multiple postings. You can, of course, run this in your favorite GUI, the MySQL command-line, or in PHPMyAdmin.

The following syntax will replace all occurences in a table. You can make it more specific by adding your own WHERE clause.

Here is the generic syntax for updating a table and replacing one string with another throughout.

MYSQLview code
UPDATE tablename SET columnname = replace(columnname,"searchfor","replacewith");

Please note: If you are searching and replacing URLs, you need to escape the forward slashes. You should, therefore, write http://google.com/ as http:\/\/google.com\/

Example WordPress replace

Here is a completed example that lets you search for text or html inside all of your posts and replace it with something else. This example will search your posts for the image ‘silverstar.gif’ and replace them all with ‘goldstar.gif’

MYSQLview code
UPDATE wp_posts SET post_content = replace(post_content,"silverstar.gif","goldstar.gif");