CODEWITHSUNDEEP
jquerymysqlsearchreplace
liquilife
liquilife

Reputation: 191

mySql query search and replace

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?

Upvotes: 1

Views: 340

Answers (3)

Martin
Martin

Reputation: 38289

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 != '')

Upvotes: 4

Unreason
Unreason

Reputation: 12704

Here's a link to REPLACE()

Upvotes: 0

Anax
Anax

Reputation: 9372

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

Upvotes: 0

Related Questions