• Skip to primary navigation
  • Skip to main content
  • Skip to primary sidebar
The Electric Toolbox Blog

The Electric Toolbox Blog

Linux, Apache, Nginx, MySQL, Javascript and PHP articles

  • Applications
  • FCKEditor
  • Apache
  • Windows
  • Contact Us
Home / How to find and replace text in a MySQL database

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.

Check Out These Related posts:

  1. jQuery Facebox Basic Example
  2. jQuery Facebox window with full height with scrollbar
  3. jQuery Facebox dialog with greyed out background
  4. jQuery Facebox dialog with opaque background

Filed Under: MySql

Primary Sidebar

Categories

  • Apache
  • Applications
  • Article
  • Case Studies
  • Email Servers
  • FCKEditor
  • HTML And CSS
  • Javascript
  • Linux/Unix/BSD
  • Microsoft SQL Server
  • Miscellaneous Postings
  • MySql
  • Networking
  • Nginx Web Server
  • Offsite Articles
  • OSX
  • PHP
  • Quick Tips
  • RFC – Request for Comments
  • SilverStripe
  • VMWare
  • VPN
  • Windows
  • WordPress

Recent Posts

  • Vim Show Line Numbers
  • Add User To Group Linux
  • Chmod 777 Tutorial
  • How to Copy Directory Linux
  • Linux create user

Copyright © 2021. ElectricToolBox. All Rights Reserved.

  • Contact Us
  • Copyright Info
  • Privacy Policy
  • Sitemap