CODEWITHSUNDEEP
mysqlreplaceimagesrc
ipixel
ipixel

Reputation: 23

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

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

Upvotes: 0

Views: 1505

Answers (1)

Hampus Brynolf
Hampus Brynolf

Reputation: 1336

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.

Upvotes: 1

Related Questions