update TABLE_NAME set FIELD_NAME = replace(FIELD_NAME,'find this string','replace it with this one');
4 thoughts on “Find and replace text in mysql”
Comments are closed.
Tech Leadership Advice & Resources
Books, worksheets, templates, frameworks and other useful resources for Chief Technology Officers (CTOs), VPs of Engineering & Technology Directors
Just what I needed thank you
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!
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
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!