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.
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 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.