Replace last characters of a STRING column in SQL

Question

First of all, I am a newbie in SQL, and I cannot realize how to solve this simple problem.

I have a table like this:

ID    NAME    PATH
----------------------------
1     Peter   img_peter.png
2     Jon     img_jon.png
3     Maria   img_maria.png

I want to modify the column PATH in order to replace the extension .png to .jpg.

Therefore, the output UPDATE should return:

ID    NAME    PATH
----------------------------
1     Peter   img_peter.jpg
2     Jon     img_jon.jpg
3     Maria   img_maria.jpg

Any ideas? Thank you

Possible duplicate of [MySQL string replace](https://stackoverflow.com/questions/5956993/mysql-string-replace) — Eric Brandt, May 01 '19 at 22:01

score 3 · Accepted Answer · answered May 01 '19 at 22:00

3

You should be able to use replace() for this:

update t
    set path = replace(path, '.png', '.jpg')
    where path like '%.png';

This assumes that .png does not occur multiple times in the string, but that seems like a reasonable assumption.

answered May 01 '19 at 22:00

Gordon Linoff

1 Answers1