Mysql replaces part of a text in the database

Question

The table (images_list is the name of the table) I have to update has over 500 rows with a certain link which I have to replace to a url connected to a local folder.

For example a field will contain www.google.com/img/test-more-text.gif and this has to be replaced to /image/test-more-text.gif. The prefix link is exactly the same for each row, the only variable part is the image name (test-more-text.gif for example is the only variable part in the example given above)

I've looked up multiple tutorials but the only things I can find replace the complete field whereas I need to keep the suffix so to speak.

This image obviously has a different name aswell so I can't simply do

UPDATE images_list 
SET image_link = '/image/test-more-text.gif' 
WHERE image_link = 'www.google.com/img/test-more-text.gif'

I know how to lookup text with the LIKE statement but I've never had to update something like this before.

If anyone knows how to do this that would safe me a ton of work

Is there really a space at the beginning of the `image_link` value? — Barmar, Apr 18 '15 at 15:41
http://stackoverflow.com/questions/16334813/sql-update-and-replace-substring — Krycke, Apr 18 '15 at 15:45
@krycke I found that question already but it completely replaced the field, what I needed was to only replace a specific part like in the answer Barmar has given — Crecket, Apr 18 '15 at 15:46
I thought it replaced 'a' with 'b' as in Barmar's suggestion. — Krycke, Apr 18 '15 at 15:53

score 4 · Accepted Answer · answered Apr 18 '15 at 15:43

4

Use the REPLACE function:

UPDATE images_list
SET image_link = REPLACE(image_link, 'www.google.com/img/', '/image/');
WHERE image_link LIKE 'www.google.com/img/%'

answered Apr 18 '15 at 15:43

Barmar

741,623
53
500
612

Mysql replaces part of a text in the database

1 Answers1