MySQL Find and Replace (Delete) Regex in phpMyAdmin

Question

I'm trying to perform a find and replace (actually, a delete) on a particular column in a MySQL database (circa 20k+ records) where the column in question contains HTML (the body text for page content in a CMS).

I need to find and replace/delete the following text from within each record and leave all of the other HTML intact (the below string appears only once per record):

<h2>Location Map</h2><p><a href="[?]" onclick="window.open(this.href); return false;">[?]</a></p>

Where "[?]" is a string of variable content and length (both alphanumeric and symbols) and the rest static content exactly as it's shown above.

I know this should be very simple and a bit of a stupid question but I'm no regular expression expert and can't quite work out what I'm doing wrong (possible not escaping enough characters or too many).

Preferably I'd like a regex I can use in phpMyAdmin to perform the find and replace.

Many thanks in advance.

MariaDB has REGEXP_REPLACE, MySQL does not.

Rick James
– Rick James

2016-03-22 23:35:53 +00:00
Commented Mar 22, 2016 at 23:35 — Rick James
– Rick James, Commented Mar 22, 2016 at 23:35

trincot · Accepted Answer · 2016-03-22 16:51:23Z

1

The find/replace statement in PHP could be something like this:

$regex = '#<h2>Location Map</h2><p><a href="(.*?)" onclick="window.open.*?>(.*?)</a></p>#';
$result = preg_replace($regex, $replace, $html);

...where $html would be the original data you retrieve from your database, and $replace the value you want to insert instead of the matched string.

For instance, if:

$html = 'all that comes before
<h2>Location Map</h2><p><a href="http://www.foofle.com" onclick="window.open(this.href); return false;">Foofle</a></p>
all that comes after';

$replace = "(link=$1, text=$2)";

$result = preg_replace($regex, $replace, $html);
echo $result;

Outputs:

all that comes before
(link=http://www.foofle.com, text=Foofle)
all that comes after

answered Mar 22, 2016 at 16:51

trincot

357k38 gold badges282 silver badges338 bronze badges

Sign up to request clarification or add additional context in comments.

Collectives™ on Stack Overflow

MySQL Find and Replace (Delete) Regex in phpMyAdmin

1 Answer 1

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

Comments

Your Answer

Sign up or log in

Post as a guest

Related