Sometimes, we need to find and replace certain strings or pieces of data in MySQL on the fly, for example if we’ve changed a Top Level domain (TLD) and want to update a bunch of links all in one go. For those times, the following command can be issued:
update [table_name] set [field_name] = replace([field_name],'[string_to_find]','[string_to_replace]');
This can be used, for example, to update strings contained in WordPress posts, such as a lot of links which you’d like to send to another domain. If you’d like specific code examples for doing this, please leave a public comment below, and I’ll update with this info.
UPDATE – for WordPress (and other CMS) – watch out for Serialized arrays
It is worth pointing out the cautionary tale here, and to say that, in WordPress, and WooCommerce, you should be very careful about doing a find and replace in this manner in the MySQL database – because of serialized arrays.
Serialized arrays are a special form of data storage which include a charcter count number in them. If you simply find and replace without updating the character count, you can run into problems. So don’t use this method in a WordPress website… do it another way!
Other ways to find and replace which work with WordPress & WooCommerce sites
Thankfully, there are some nice plugins which allow you to find and replace within WordPress in a nice way, quietly updating the serialized array in the correct manner, and generally causing things not to fail, at all.
Recommended search and replace plugins for WordPress
Two we like for this task are:
Both plugins are made by Delicious Brains and they both do similar things, and do it “right”. So double thumbs up as a method for finding and replacing data in WordPress. This works really well if you just changed your URL or you made a change in structure and want to update all your internal linking, for example.