mySql query search and replace

Question

Hey folks, I have a field within a table that I need to do a search and replace on, though it's a bit tricky and beyond my measure of querying skills. Basically, the contents of a field may look like this:

image1.jpg
image2.jpg
image3.jpg
image4.jpg
image5.jpg

I need to do a search and replace that would replace each line with something like:

<img src="http://www.domain.com/image1.jpg" />
<img src="http://www.domain.com/image2.jpg" />
<img src="http://www.domain.com/image3.jpg" />
<img src="http://www.domain.com/image4.jpg" />
<img src="http://www.domain.com/image5.jpg" />

The image titles are completely random and could be .gif, .jpg or pngs. The table name is "exp_channel_data" and the field name is "field_id_8" and I'd be running the query in phpadmin. Is the above search and replace possible?

Answer 1

Yes, it is. I hope your Regular Expression skills are up-to-date.

Answer 2

Here's a link to REPLACE()

Answer 3

I believe you can replace line breaks with a closing + opening tag and then wrap the result in an img tag to get the desired result:

UPDATE exp_channel_data
SET field = CONCAT(
    '<img src="http://www.domain.com/',
    REPLACE(field_id_8, '\n',  '" />\n<img src="http://www.domain.com/'),
    '" />')
WHERE (field_id_8 IS NOT NULL) AND (field_id_8 != '')