Web Development Blog

Web Development » WordPress » Find and replace text in mysql

26 May, 2006

Find and replace text in mysql    

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

Found this article useful? Share it with your friends:

Love what you've just read? Subscribe to our newsletter to receive tips, resources and special offers related to web development & design.

Your name:   Your email:  

Other similar posts that you might be interested in:

• Apending text to a field which isn’t empty (mysql)
• Backuping and restoring a single table using mysqldump
• Remove html tags from a string
• LEFT JOIN and RIGHT JOIN in mysql
• eZ Publish extensions for Flickr and YouTube – Download
• MiniAjax
• Customising reCAPTCHA Theme

4 Responses to “Find and replace text in mysql”

1. Andy says:

   April 8, 2009 at 2:11 am

   Just what I needed thank you
2. Benxamin says:

   April 9, 2009 at 7:40 am

   Oh HELL YES. Greatly appreciated.

   I was pushing a WordPress site from development server to production server, and all the functions that called the ‘guid’ meta data from the page array were pointing back at the dev server.

   This beats updating per line.

   UPDATE wp_posts SET guid = REPLACE(guid, ‘dev.server.com’, ‘prod.server.com’);

   Like a charm. Thanks again!
3. MJ7 says:

   December 9, 2009 at 10:34 pm

   Great little function – have expanded it a little to find and replace across a whole database – http://www.mjdigital.co.uk/blog/search-and-replace-text-in-whole-mysql-database/

   Perfect for moving a WordPress blog from a development server to a live server and changing the domain name
4. walmley web design says:

   February 1, 2011 at 11:29 pm

   I recently wanted to replace a string within MySQL on the fly, but the field could contain 2 items. So I wrapped a REPLACE() within a REPLACE(), such as:

   REPLACE(REPLACE(field_name, “what we are looking for”, “replace first instance”), “something else we are looking for”, “replace second instance”)

   This is the syntax I used to detect a boolean value:

   REPLACE(REPLACE(field, 1, “Yes”), 0, “No”)

   Hope this helps!