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), ',', '');

Peres

Thank you! The comment UPDATE mytable SET column = SUBSTR(column, 1, LENGTH(column) - 1) WHERE SUBSTR(column, -1, 1) = ',' worked for me. The others still blanked out the whole line instead of just the last ','. – Peres

You're getting null cause the function REPLACE returns null - check my updated answer – Onehorse

Excellent thanks! Would a similar function work if I was trying to remove a ',' that was the first character of a string? (,A,B,C) – Peres

@RahulTripathi the "thank you" wasn't for you - I caught the "where" issue and Quassnoi caught the "replace" thing (and you changed your answer after you saw it!). Please don't take credit that doesn't belong to you! – Onehorse

that would actually be even "easier" cause you can do: UPDATE table SET column = SUBSTR(column, 2) WHERE SUBSTR(column, 1, 1) = ','; – Onehorse

They both work perfectly! – Peres

Answer 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 ","

Onehorse

Or possibly WHERE column LIKE '%,' – Pleader

Answer 2

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

Schrader

I think this answer look more like a comment than an answer. – Saleme

@AlikElzin-kilaka, are you bothered more with answer's brevity or utility? Both are superior, imho. ) – Schrader

Answer 3

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

Auburn

Answer 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));

Soelch

Answer 5

To change a single instance of a character in a specific position in a string, let's say a code like this 'IM0B010011'. I would use this code:

select substr('IM0B010011',1,length('IM0B010011')-1) || (substr('IM0B010011',-1)+1)
from dual;

Result: 'IM0B010012'

Siouan