CODEWITHSUNDEEP
replacesql-updateoracle9isubstr
PhelpsK
PhelpsK

Reputation: 217

Oracle - Update string to replace only the last character

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

Upvotes: 5

Views: 43860

Answers (5)

Moheb Youssef
Moheb Youssef

Reputation: 1

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'

Upvotes: 0

Vadzim
Vadzim

Reputation: 26160

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

Upvotes: 9

fse
fse

Reputation: 11

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

Upvotes: 1

Nir Alfasi
Nir Alfasi

Reputation: 53525

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

Upvotes: 3

Quassnoi
Quassnoi

Reputation: 425321

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

Upvotes: 1

Related Questions