Globally match and replace text or string with MySQL query

In doing some research looking into possible ways to batch replace MSU Library URL strings in our various MySQL databases, I was able to construct this little nugget that will do the trick:

UPDATE table_name SET column_name = REPLACE(column_name, ‘old_string/text’, ‘new_string/text’);

A quick explanation:

table_name is the database table you need to edit.
column_name is the database table column name to edit.
old_string/text is the original string or text to match and replace.
new_string/text is the new string or text you want to add.

It’s short, sweet, and delightfully efficient. The longevity and active development around MySQL and SQL always surprise me. If you have a business problem, chances are there is a function or built procedure already in the code ready to answer the problem.

Tips and tricks just like this can be found pretty regularly by trolling the MySQL manual and forums. Another method for keeping up and learning is “tagwatching” on del.icio.us. Some possibilities for watching the tag “mysql”:

HTML page: http://del.icio.us/tag/mysql

RSS feed: http://del.icio.us/rss/tag/mysql

That’s right. With that last RSS URL, you can subscribe to a tag and watch as the latest posts come into your feedreader of choice. (del.icio.us has a feed for all of its tags.) Good stuff for keeping the learning going.