Home / server / Questions / 270575
Accepted
javipas
Asked: 2011-05-18 06:55:03 +0800 CST

Replace text on MySQL - Wildcards to replace certain variable parts?

  • 772

I'm trying to apply a text replace on my WordPress MySQL database. I need to delete part of the current images html code, but this part of the text is variable, althouth there is a part that never changes.

Is there some wildcard that I can use on a MySQL replace?

Example: I would like to replace this:

<a target="_self" href="actualidad/tecnologia/4455-windows-7-surpasses.html"><img src=”http://www.example.com/files/HIDDEN_264_23662_FOTO_win01.jpg" width="215" height="156" /></a>

to

<img src=”http://www.example.com/files/HIDDEN_264_23662_FOTO_win01.jpg" width="215" height="156" />

And delete the href part, so I was thinking in a replace similar to this one: 

update wp_posts set post_content = REPLACE 
(post_content, '<a target="_self" href=*<img','<img');

Is there a "*" (or regexp) that takes whatever there is between the 'href' and the '

mysql wordpress wildcard replace

3 Answers

  1. Best Answer

    I usually cheat and dump the database to text, and then use sed (or Notepad++ if the database is small enough). Barring that, it's easier to do it programmatically using Perl or Python.

    Doing this kind of manipulation with straight SQL is painful. If you DO do it, I'd recommend strongly that you avoid REPLACE. This isn't what it's normally used for. If you're going to do a bulk change on existing rows, use UPDATE by itself instead.

    • 3

  2. % does not work in this scenario?

    • 1

  3. Unfortunately, there is no further regular expression API available.

    People have taken cracks at it in the MySQL 4.0 days, but such efforts did not make it into the mainstream MySQL binaries.

    http://www.php-groupies.de/blogs/archives/17-Regular-Expression-Functions-for-MySQL.html

    http://forge.mysql.com/worklog/task.php?id=353

    Your UPDATE statement should be enough.

    update wp_posts set post_content = REPLACE (post_content, '<a target="_self" href=*<img','<img');

    Even if such API existed, there is no sense in applying it any better than you already have. Your UPDATE does a full table scan. Applying a WHERE using REGEXP would still results in a full table scan. You original UPDATE is best for the given scenario.

    • 1