what will translate function do if I want to change some chars to nothing?

Question

I have a sql statement:

select translate('abcdefg', 'abc', '') from dual;

Why the result is nothing? I think it should be 'defg'.

Answer 1

From the documentation:

You cannot use an empty string for to_string to remove all characters in from_string from the return value. Oracle Database interprets the empty string as null, and if this function has a null argument, then it returns null. To remove all characters in from_string, concatenate another character to the beginning of from_string and specify this character as the to_string. For example, TRANSLATE(expr, 'x0123456789', 'x') removes all digits from expr.

So you can do something like:

select translate('abcdefg', '#abc', '#') from dual;

TRANSLATE('ABCDEFG','#ABC','#')
-------------------------------
defg           

... using any character that isn't going to be in your from_string.