Oracle - Update string to replace only the last character

Question

I have the following string in an Oracle 9i database:

A,B,C,

I need to replace all instances of ',' when it is the last item in the string. I have come up with the following statement but it deletes everything in the field not just the comma. Any suggestions?

UPDATE table SET column = REPLACE(SUBSTR(column, -1, 1), ',', '');

Solution 1

You forgot to add the condition: WHERE SUBSTR(column, -1, 1) = ','
Quassnoi caught another issue - REPLACE returns null - you can't use it inside the "set"

Full sql:

UPDATE table SET column = SUBSTR(column, 0, length(column)-1) 
WHERE SUBSTR(column, -1, 1) = ',';

This will make sure you're doing the substitute only in rows that has values that ends with ","

Solution 2

rtrim(column, ',') is both efficient and much shorter

Solution 3

UPDATE  mytable
SET     column = SUBSTR(column, 1, LENGTH(column) - 1)
WHERE   SUBSTR(column, -1, 1) = ','

Solution 4

If you want to refer 'column' only 1 time in your query, just do as following:

UPDATE table SET column = REVERSE(SUBSTR(REVERSE(column), 2));

View more solutions