How to find and replace text in a MySQL database

There are a few posts on this blog about the Facebox jQuery plugin, and as I discovered this morning the website URL for the plugin has changed so I needed to update all links to it. The quickest way to do this was to update the MySQL database directly using UPDATE and REPLACE to find the old URLs and replace them with the new URLs. So here's how to find and replace text in a MySQL database.

Database structure

The example query below updates the "content" and "description" fields of the "content" table. Obviously your database will have different fieldnames and you may only want to update one field, or maybe update more fields. Simply modify the query to suit.

MySQL UPDATE and REPLACE

Without the example fieldnames and content to change, the query looks like this:

UPDATE [tablename]
SET [fieldname] = REPLACE([fieldname], 'text to find', 'text to replace with')
WHERE [fieldname] LIKE '%text to find%'

You don't necessarily have to add the WHERE LIKE clause at the end, because if the text to find isn't there the row won't be updated, but it should speed things up.

Now here's the same query using my tablename and fields, and the find and replace URLs. I needed to replace http://famspam.com/facebox with http://defunkt.io/facebox/

UPDATE content
SET description = REPLACE(description, 'http://famspam.com/facebox', 'http://defunkt.io/facebox/'),
content = REPLACE(content, 'http://famspam.com/facebox', 'http://defunkt.io/facebox/')
WHERE description LIKE '%http://famspam.com/facebox%'
OR content LIKE '%http://famspam.com/facebox%'

Warning

If you're going to update your database like this, I would recommend testing the query on a copy of the database first so you can double check you've run the query correctly and that the resulting data looks correct. Once checked, run it against your production database.

Related posts:

• String concatenation with MySQL
• Copy a table in MySQL with CREATE TABLE LIKE
• Using SELECT REPLACE with MySQL