MySQL regex replace?

Question

I've got a table with blob text fields. This blob has a lot of html in it. One of the pieces of html is an h2. I would like to find all instances of a word in the h2 tag, and replace it with another word (while leaving other words the same).

For example, I would like to replace the h2 "wiggles" with "bumbles" in the following:

Before:

<h2>This is some wiggles html!</h2>
<p>And here is some more wiggles html that could be ignored</p>
<h2>And this is a decoy h2</h2>

After:

<h2>This is some bumbles html!</h2>
<p>And here is some more wiggles html that could be ignored</p>
<h2>And this is a decoy h2</h2>

A pitfall I'm concerned about is the regex not stopping at the end of the first h2, and rather continuing through to the last closing of it.

I have access to shell and phpmyadmin.

Answer 1

There is no regexp replace feature in mySQL proper: The regex functions are match only.

There seems to be a user defined function that adds the functionality somehow, but it requires re-compiling mySQL and is probably not an option.

I'd recommend doing this using a programming/scripting language like PHP, using its built-in regex replace functions to change the content, and update the records.

Edit: overlooked the php tag.

Answer 2

Hi,

Html is not a regular language therefore trying to parse it with regex is not the best option. In my opinion i would want to leverage a html parser to do this job. Here is a sample parser.

Enjoy!