replace two characters in one cell

Question

I am using this query to replace one character in a cell

select replace(id,',','')id from table

But I want to replace two characters in a cell. If the cell is having this data (1,3.1), and I want it to look like this (131). How can I replace two different characters in one cell?

Answer 1

Use TRANSLATE instead of REPLACE(). It replaces each occurrence of a character in the first pattern with its matched character in the second. To remove characters, simply leave cut short the replacement string:

select translate(id, '1,.', '1') id from table

Note that the second string cannot be null. Hence the need to include 1 (or some other character) in both strings.

Find out more.

Obviously the more characters you need to convert/remove the more attractive TRANSLATE() becomes. The main use for REPLACE is changing patterns (such as words) rather than individual characters.

Answer 2

Can use

select replace(translate(id,',.',' '),' ','') from table;

or

select regexp_replace('1,3.1','[,.]','') from dual;

or

select replace(replace(id,',',''),'.','') from table;