Translate oracle function

Learn how perform one to one character replacement using translate function with example queries.

TRANSLATE function performs character to character replacement and returns a string value.

Syntax

TRANSLATE(string, from_string, to_string)

string-is the string that to be translated.
from_string-is a string which contains characters that should be replaced.
to_string-is a string that matches from_string argument.

For example consider a string “1234561511”.

Now find the sub-string 123 in the main-string and replace the string with abc.

1–a
2–b
3–c

after performing character wise replacement the returning string forms as below

abc456a5aa

The argument from_string can contain more or less characters than the argument to_string.

In the case of having more characters, the extra characters at the end of from_string have no corresponding characters in to_string.If these extra characters appear in main string, then they are removed from the return value.

For example

SELECT TRANSLATE('TRANSLATE','RNLT','123') from dual;

Output
1A2S3AE

In the above example for Character ‘T’ in from_string, there is no corresponding character in to_string.

Now the character ‘T’ is replaced with NULL value in main string.

In the case of having less characters, the extra characters at the end of to_string is omitted.

SELECT TRANSLATE('TRANSLATE T','RNLT','12345') from dual;

Output
41A2S3A4E 4

If the to_string or from_string is empty, then return value of translate function is null

SELECT TRANSLATE('TRANSLATET','RNLT','') from dual;

Output

null

SELECT TRANSLATE('TRANSLATET','','RNLT') from dual;

Output
null