Replacing text in a mySQL database table is trivial thanks to the built-in SQL command REPLACE() that lets you manipulate text data within a table that matches a particular string, and replace it with another string.

I have been playing extensively with the theme on this website recently and one of the cute little hacks I put in has been completely breaking the correct rendering of the page in Safari. I was brute-forcing a solution rather than relying on a style sheet to do the heavy lifting for me. Fortunately, I saw the error of my ways and decided to back out my changes.

But unfortunately for me, in my brute force solution I had neglected to put style sheets on to about 300 different uses of the HTML tags for <pre><code> meaning I would have to go through by hand and replace each and every tag, by hand. Surely there’s a better way.

SQL To The Rescue!

The REPLACE() command works on all SQL databases, not just mySQL, so if you are using Microsoft SQL Server and need to do this in T-SQL, it’s the same piece of code.

The syntax of REPLACE is:

REPLACE(<field name>, <search string>, <replace string>)

which is pretty easy to wrap your head around. It’s just like a string replace in a regular programming language, except that the first parameter is the name of the field in the table in the database to operate on rather than a string variable name.

As an example:

UPDATE wp_posts SET post_content = REPLACE(post_content, ‘Hello’, ‘Oh Hai!’)

would replace all occurrences of the world “Hello” with the LOLCat “Oh Hai!” in every post.

In my case I needed to change all occurrences of <pre><code> to <pre class=”code_block”><code> in my WordPress posts table. So my SQL command became:

UPDATE wp_posts SET post_content = REPLACE(post_content, ‘<pre><code>’, ‘<pre class=”code_block”><code>’)

Which in about a fifth of a second on the mySQL database server replaced more than 300 instances of the <pre><code> tags combination.

Subscribe to the RSS feed or follow me on Twitter to stay up to date!