Quickly update a database field in MySQL by replacing one value with another with find and replace.

This is a MySQL query I find myself using in a bunch of situations when there are multiple users contributing to articles or other means of providing content.  It’s usually to replace absolute links like “http://tools.belchamber.us/link-here/” to just “/link-here/”, but I found it’s useful for many other things.  Recently, I had to import thousands of articles from an older site into a new magazine site and ran into this, and other problems where this query made short work out of issues stemming from people hard-coding font size and inline styling.  Back then, this was the way to do things, but honestly, if you don’t encounter content these days where the only “inline styling” is assigning a certain part of the code the proper <H1…H6> tags or a class, then sit down with your content team and have a serious discussion because it will save you hours of re-editing content later the next time you update your website!

Using this “find and replace” query can quickly remove inline styles embedded into content with a class — a much better solution since, who knows, maybe you want to change the font, color and style of all the sub-headers later, so if everything is assigned a CSS class, you just change the class definition in the CSS instead of, oh, I don’t know, EDITING THOUSANDS OF PAGES MANUALLY?!!!!!!

Here’s an example, some goofball hardcodes something like this:

<p style="font-size:11pt;font-family:Arial,sans-serif;color:#999999;font-weight:bold">I am an idiot for writing content with inline styles.</p>

With search and replace, you can quickly replace the inline attribute ‘style=”font-size:11pt;font-family:Arial,sans-serif;color:#999999;font-weight:bold”‘ with something elegant and centralized with a class:  ‘class=”no-more-inlines”‘.  So, following the pattern of this basic MySQL find and replace query below, here is how you could replace the inline style completely with a class.  You just have to make sure a class isn’t also already assigned to the tag!

UPDATE [table_name] SET [field_name] = REPLACE([field_name], “find text”, “replace text”);

UPDATE my_table SET post_content = REPLACE(post_content,'style="font-size:11pt;font-family:Arial,sans-serif;color:#999999;font-weight:bold"','class="sub-heading-1"')

Before running these types of queries, keep in mind it is destructive so you should always back up the table before running this query, in case of any unintended consequences.

About Author:

Senior Cloud Software Engineer and 25+ years experienced video production, video editing and 3D animation services for a variety of global clients including local video production here in Jacksonville, Florida.

Leave a Comment

Your email address will not be published. Required fields are marked *