MySQL Search & Replace

It seems like every six months or so I need to do some site wide replacing (not often enough to commit this tip to memory it seems). If the site was built as flat HTML pages, I’ll use the find/replace feature in whatever editor I am using at the time. These days—more often than not—the actual site content is somewhere in a MySQL database. When that is the case, this one-liner comes in handy.

update table set field = replace(field,'find_this','replace_with_this');

As with any database change, you should first back up your database, or at the very least duplicate the table (with the content—of course).

Let’s say you wanted to change the words “Web site” to the more commonly used “website” in all of your site content. If the table holding the content is named tbl_content and the field was named page_content, then your MySQL command would look like this:

update table_content set page_content = replace(page_content,'Web site','website');