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?

The database is part of a CMS. After some version updates, etc etc, running a query through phpadmin is definitely my best and only option. — liquilife
– liquilife, Commented Aug 2, 2010 at 15:03

Martin · Accepted Answer · 2010-08-03 05:57:05Z

4

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

edited Aug 3, 2010 at 5:57

answered Aug 2, 2010 at 15:05

Martin

38.5k15 gold badges75 silver badges83 bronze badges

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

3 Comments

Unreason Over a year ago

+1, I was just about to expand my answer to basically the same thing.

liquilife Over a year ago

This worked beautifully good sir! Thank you so much. Much more efficient then the query i was trying to string together :)

liquilife Over a year ago

Actually, how would this be modified to skip over any of the rows where that field is NULL or empty?

Anax · Accepted Answer · 2010-08-02 14:44:14Z

0

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

answered Aug 2, 2010 at 14:44

Anax

9,4225 gold badges37 silver badges68 bronze badges

1 Comment

liquilife Over a year ago

Thanks. Unfortunately my Regular Expression skills are not up to date. I just can't seem to wrap my head around how to do this. Can you show me a quick dirty example of how to do something like this?

Unreason · Accepted Answer · 2010-08-02 15:03:28Z

0

Here's a link to REPLACE()

answered Aug 2, 2010 at 15:03

Unreason

12.7k2 gold badges39 silver badges51 bronze badges

Collectives™ on Stack Overflow

mySql query search and replace

3 Answers 3

3 Comments

1 Comment

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

3 Answers 3

3 Comments

1 Comment

Comments

Your Answer

Sign up or log in

Post as a guest

Related