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'.

Bradleigh

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.

Aberration

@Alex, +1 for the quote from documentation. – Trisect

Answer 2

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

To add to Alex's answer, you could use any character(allowed in SQL) for that matter to concatenate to remove all the characters. So, you could even use a space instead of empty string. An empty string in Oracle is considered as NULL value.

So, you could also do -

SQL> SELECT TRANSLATE('abcdefg', ' abc', ' ') FROM dual;

TRAN
----
defg

SQL>

Which is the same as -

SQL> SELECT TRANSLATE('abcdefg', chr(32)||'abc', chr(32)) FROM dual;

TRAN
----
defg

SQL>

Since the ascii value of space is 32.

It was just a demo, it is better to use any other character than space for better understanding and code readability.

Trisect