mySQL Query to Extract/Replace src parameter from <img> tag in field data

Question

I have a situation where I'm upgraded a CMS based website that has had some major changes over the years on how to handle certain field types. On in particular is images. Originally an tag in a text field was used to store and image. Now we've moved to a more dynamic method where we use a preset file directory and just the image file name.

Currently in my 'exp_weblog_table' I have a field 'field_id_8' which is a text field holding something like the following:

<img src="{filedir_2}V55-Kaos-Chrome.jpg" width="400" height="400" />

Now my SQL query skills are pretty much non-existent, so need a hand in stripping out everything in the field except the contents of the img tag's src parameter. SO based on the above example of existing data, I ideally need the following left in the field data:

{filedir_2}V55-Kaos-Chrome.jpg

There's a number of other fields that use the same method, but I should be able to figure it out once I have something to work from.

I'll be using phpMyAdmin to make these updates to the DB table fields.

Thanks in advance

Brendan

mysql has no html processing capabilities beyond basic string manipulation. you're in for a long set of substr/left/right operations. — Marc B
– Marc B, Commented May 27, 2012 at 4:43
@ipixel This other answer might be helpful: stackoverflow.com/questions/19373796 — Mosty Mostacho
– Mosty Mostacho, Commented Oct 17, 2013 at 5:56

Hampus Brynolf · Accepted Answer · 2012-05-27 09:23:14Z

1

There are no regexp replace function in Mysql. Instead, export the table, do the replace on the .sql-file, truncate the table and finally execute the sql again.

The regexp (that you can run in various texteditors such as Textwrangler (Mac) or Notepad++ (PC)) :

search for:

<img.+?src=[\"'](.+?)[\"'].+?>

replace

\1

or

$1

depending on your editor.

answered May 27, 2012 at 9:23

Hampus Brynolf

1,33611 silver badges20 bronze badges

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

Collectives™ on Stack Overflow

mySQL Query to Extract/Replace src parameter from <img> tag in field data

1 Answer 1

Comments

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

Comments

Your Answer

Sign up or log in

Post as a guest

Linked

Related