0

I have a problem that is almost exactly as this:

http://www.experts-exchange.com/Web_Development/Web_Languages-Standards/PHP/Q_27928324.html

I am not about to give my credit card information to a website to find the answer to this issue.

In my table named 'product' have around 7000 rows in a column named 'image' in my database that each have file paths for images ending in .jpeg. Some image names are 234234_15.jpeg, some are wdcaas_ad_3_15_mmm.jpeg, etc etc. But the file structure is different for each image.

I need to make a script to update all rows and leave only the image name. Preferably I would eventually like to replace all file structures with the folder /data/, and have an example of /data/imagename.jpeg

I found this Remove part of string including a specific character from a string using MySQL and it might help? I am not sure how to use it.

help!

Improve this question
1
  • Regarding experts exchange, if you scroll down to the bottom of the page, you will see all the correspondence on the issue. You can skip the adverts and of course you don't have to pay. Commented Apr 4, 2013 at 3:08

1 Answer 1

Reset to default
0

You can do this with reverse . . .

select reverse(substring_index(reverse(image), '/', 1))

If you want to actually update the values . . .

update t
    set image = reverse(substring_index(reverse(image), '/', 1))

To add 'data/' in front, use concat:

update t
    set image = concat('data/', reverse(substring_index(reverse(image), '/', 1)))
Improve this answer
Sign up to request clarification or add additional context in comments.

4 Comments

So this will change all rows in the 'image' column?
@Chris . . . The second version will update the values. The first returns the image name so you can see if it works.
it worked perfectly. Now I need to add 'data/' infront of each string. I guess concat will do the job?
@Gordon.. Thanks man, you have saved me at least a week's worth of invested time to go over every image and change it's link

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

Start asking to get answers

Find the answer to your question by asking.

Ask question

Explore related questions

See similar questions with these tags.