Translate function

In Oracle/PLSQL, the translate function replaces a sequence of characters in a string with another set of characters. However, it replaces a single character at a time. For example, it will replace the 1st character in the string_to_replace with the 1st character in the replacement_string. Then it will replace the 2nd character in the string_to_replace with the 2nd character in the replacement_string, and so on.

syntax:
translate('srikar','search_characters','replace_characters')
SQL> select translate('srikar','sr','12') from dual;

TRANSLATE('SRIKAR','SR','12')
-----------------------------
12ika2

if length(search_characters)>length(replace_characters) then
then the characters from search_characters that don't have replace_characters are replaced with null

e.g.
SQL> select translate('srikar','srik','12') from dual;

TRANSLATE('SRIKAR','SRIK','12'
------------------------------
12a2

if length(search_characters)then the extra characters are omitted

SQL> select translate('srikar','sr','1234') from dual;

TRANSLATE('SRIKAR','SR','1234'
------------------------------
12ika2

if we provide null in the replace_characters then we get the output as null
SQL> select translate('srikar','s','') from dual;

TRANSLATE('SRIKAR','S','')
--------------------------

so the trick here is we should pass atleast one value as shown below to avoid returing of null value.

SQL> select translate('srikar','2s','2') from dual;

TRANSLATE('SRIKAR','2S','2')
----------------------------
rikar