MySQL Replace

[Pete]

Hi guys

I've been trying to work out how to replace empty <p> tags via mySQL. Sometimes they look like this:

<p> </p>

and other times like this:

<p> </p>

Using this query doesn't work for some reason:

UPDATE table SET field = REPLACE(field, "<p> </p>", "");

No matter what I do it seems to return zero results, whichever version of the empty tags I try and replace.

The scenario is actually that after a test forum upgrade (the ProcessWire forums actually) extra <p> tags are in the DB as the spacing in this stupid editor has zero padding, so what we all thought were linebreaks are extra paragraphs (well, everyone except those who looked at the source code ).

Ideally, a more foolproof way of doing this would be to only remove the first empty paragraph following a paragraph with content in, so that we are not also deleting extra spacing people may have intentionally added. I think that would be a regex though and I'm not even sure you can do those in MySQL directly? I'm basically looking for a solution that will work on >90,000 rows without taking all day

Thanks in advance guys!

[DaveP]

I think that would be a regex though and I'm not even sure you can do those in MySQL directly?

https://dev.mysql.com/doc/refman/5.1/en/regexp.html#operator_regexp

[SiNNuT]

That is strange, i don't see anything wrong with your query. When i run it on my two-row table it works as expected.

UPDATE mytest SET mytext = REPLACE(mytext, '<p> </p>', '')

Result, 2 rows affected

<p>This is a test</p><p> </p><p>Cool!</p> after update: <p>This is a test</p><p>Cool!</p>

<p>Another test</p><p> </p><p>Cool!</p> after update: <p>Another test</p><p>Cool!</p>

EDIT

For you second question: I'm sure that you've already Google'd it yourself but MySQL does not seem to support regexp based replaces.

So unless we're lucky enough to be running on MariaDB https://mariadb.com/kb/en/mariadb/regexp_replace/ , the fastest way i think is to just dump the database and perform the regexp replace with a different tool/php/mariadb.

[Pete]

Hmm... why didn't I think of the text editor option? I know it will be a few hundred MB but my PC can handle it