mysql find/replace string in fields

Question

I have a table which has a few varchar fields named like this:

image_fullres | image_highres | image_mediumres | image_lowres | image_thumbnail

I currently have the same value for each of these fields(differing by row), so image_fullres has the same path as image_lowres and so on.

an example value for these paths would be http://mysite.com/images/image1.jpg

I would like to have each of these fields to contain a different and appropriate path name, so:

image_fullres has value of http://mysite.com/images/fullres/image1.jpg
image_highres has value of http://mysite.com/images/highres/image1.jpg
image_mediumres has value of http://mysite.com/images/mediumres/image1.jpg

and so on...

Since all of my data is already in the database with duplicate paths by row, what SQL can I execute that would change the value of each field's data to an appropriate path?

user479911 · Accepted Answer · 2011-02-17 21:32:03Z

12

Example:

UPDATE table 
SET image_fullres = REPLACE(image_fullres, 'http://mysite.com/images/', 'http://mysite.com/images/fullres/')

This will update the image_fullres column for all rows in your table. You can do one such statement for each column, or combine them into one:

UPDATE table 
SET
image_fullres = REPLACE(image_fullres, 'http://mysite.com/images/', 'http://mysite.com/images/fullres/'),
image_highres = REPLACE(image_highres, 'http://mysite.com/images/', 'http://mysite.com/images/highres/'),
...
...

answered Feb 17, 2011 at 21:32

user479911

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

Collectives™ on Stack Overflow

mysql find/replace string in fields

1 Answer 1

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

Comments

Your Answer

Sign up or log in

Post as a guest

Related