Replacing last 3 characters of values

I presumably appended .png to all entries in the column image like this:

update genres set image=image||'.png'.

I then realised that the images I'm referencing will be jpegs; and I'm too lazy to go and convert them; so I'd rather spend 10 minutes trying to form an SQL query and then result to serverfault; so I want to change them again.

I've tried:

update genres set image = image.slice(0,-4)||'.png';

and then I remember it's SQL, everything is as confusing as its string-concatenation operator.

So then I looked up a few string functions and came up with this:

UPDATE genres SET image = right(overlay(right(image) placing right('.png') FROM 0));

But it just doesn't work. There's always someone who's been working with SQL for 20 years that can write this paragraph in 10 characters of functional code.

I'm postgresql, it's basically the same thing.

There are several ways to do that.

The first one that Rick already mentioned will work on Postgres as well:

update genres
   set image = replace(image, '.png', '.jpg');

But that will not work if the file names contain .png before the end. To be sure to only replace that value at the end you can use a regular expression:

update genres
   set image = regexp_replace(image, '(.*)\.png

or you can simply use the first "length - 3" characters and append the new extension to that.

update genres
   set image = left(image, -3)||'png';


Alternatively as you seem to not like the || operator that has been defined for SQL 30 years ago:

update genres
   set image = concat(left(image, -3), 'png');
, '\1.jpg');

or you can simply use the first "length - 3" characters and append the new extension to that.

%%CODEBLOCK_2%%

Alternatively as you seem to not like the || operator that has been defined for SQL 30 years ago:

%%CODEBLOCK_3%%

StackExchange Database Administrators Q#151641, answer score: 4