0

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

ipixel
  • 23
  • 3
  • 1
    mysql has no html processing capabilities beyond basic string manipulation. you're in for a long set of substr/left/right operations. – Marc B May 27 '12 at 04:43
  • @ipixel This other answer might be helpful: http://stackoverflow.com/questions/19373796 – Mosty Mostacho Oct 17 '13 at 05:56

1 Answers1

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.

Hampus Brynolf
  • 1,296
  • 11
  • 20