When working with databases, one of the tasks that may popped up every once in a while is the need to replace some little snippet of text and all its occurrence throughout the table, to a different text. Simple enough to do manually, but too time-consuming. Thankfully, there’s a handy query to make this task a breeze.

UPDATE `table_name` SET `field_name`= REPLACE (`field_name`, 'search for', 'replace to');

The query is pretty self-explanatory. As with all UPDATE operation, you can add a WHERE clause to have more control over the outcome of the query.

The most common scenario where I found this to be useful is when moving a WordPress installation from a development server over into a live server, where I have to replace all occurrence of the former URL to the latter in order to ensure images in posts are pointing correctly. Here’s how the query may look.

UPDATE `wp_posts` SET `post_content`= REPLACE (`post_content`, 'http://devserver.com/', 'http://liveserver.com/');

Just run it using your favorite database admin tool and you’ll be glad in no time. Have a nice day!