Search and replace for MySQL

I often have to search and replace through lots rows in MySQL, so this is useful:

update TABLE_NAME set FIELD_NAME = replace(FIELD_NAME, 'find this string',
'replace found string with this string');

A good example of this is replacing URL strings in CMS databases. I’ll show a simple Magento one. Magento stores all the URLs in ‘core_config_data’ in the ‘value’ column. Here is an easy to way to see them:

SELECT * FROM core_config_data WHERE value LIKE 'http%';

Lets assumes this spits out some URLs:

http://www.example.com/
http://www.example.com/js/
http://www.example.com/css/

And we want to change the domain to ‘example2’:

UPDATE core_config_data SET value = REPLACE( value , 'example' , 'example2' );

Use the SELECT … LIKE statement to check your work. Please be careful, this is a brute force search and replace.

 

Update: Give credit where credit is due … how-to-find-and-replace-text-in-my-database-using-sql

Leave a Reply

Please log in using one of these methods to post your comment:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out /  Change )

Google+ photo

You are commenting using your Google+ account. Log Out /  Change )

Twitter picture

You are commenting using your Twitter account. Log Out /  Change )

Facebook photo

You are commenting using your Facebook account. Log Out /  Change )

Connecting to %s

This site uses Akismet to reduce spam. Learn how your comment data is processed.