2

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 '

javipas
  • 1,332
  • 3
  • 23
  • 38

3 Answers3

3

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.

Satanicpuppy
  • 5,946
  • 1
  • 17
  • 18
  • I guess a SUBSTR or MID approach has more clarity than an outright UPDATE. UPDATE is simpler, but as you just said, avoiding REPLACE is better. +1 for you. – RolandoMySQLDBA May 17 '11 at 15:36
  • Thanks for the suggestion... I'm affraid I'm just a novice in regexp, and I don't know how to program/use Perl or Python to do this. Getting a SQL backup and opening it on notepad++ was one of my ideas, but I didn't know how to replace this kind of 'wildcard sentences' either :( – javipas May 17 '11 at 15:40
  • @javipas: MySQL is pretty friendly about letting you export to a massive SQL statement. Most of the free tools support this. You can download the trial version of Navicat, and use export it most any file format. – Satanicpuppy May 17 '11 at 15:58
  • There's no problem with the SQL dump. I can do it both through phpmyadmin or through the mysql client on my server. The problem I have is what to do with it after that :( I don't know how to solve the search and replace problem with Perl/Python. Any other alternative? Maybe a specific solution with some other text editor supporting regexp? – javipas May 17 '11 at 16:28
  • @javipas: Notepad++ supports regex, huge files, macros, you name it. You could also dump into excel (or anything) using ">" as a column delimiter. – Satanicpuppy May 17 '11 at 16:40
  • Thanks for the tip. I'll try to learn how to explore those options. It's a pity that MySQL doesn't allow these kind of things :( – javipas May 28 '11 at 12:19
  • @javipas: No databases do, really. They're for storing, sorting, and returning data. Updates are cumbersome, and complex ones are almost always done using something other than SQL. – Satanicpuppy May 29 '11 at 14:48
1

% does not work in this scenario?

3molo
  • 4,330
  • 5
  • 32
  • 46
1

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.

RolandoMySQLDBA
  • 16,544
  • 3
  • 48
  • 84