TRANSLATE
TRANSLATE(string, from_string, to_string)

Modifies a string by translating one set of characters into another.

string

The input character string.

from_string

The set of characters you want to translate.

to_string

The set of new characters that will replace from_string. Each character of from_string in the input string is replaced by the corresponding character from the to_string. Characters in from_string with no corresponding elements in to_string are deleted.

Examples

The following two examples translate the digits through 9 into the letters a through j. In the second example, the from_string contains a period and the to_string does not. This situation causes the period to be deleted:

SQL> SELECT TRANSLATE('123.45','0123456789','abcdefghij') FROM dual;

TRANSL
------
bcd.ef

SQL> SELECT TRANSLATE('123.45','0123456789.','abcdefghij') FROM dual;

TRANS
-----
bcdef

Get Oracle SQL: the Essential Reference now with O’Reilly online learning.

O’Reilly members experience live online training, plus books, videos, and digital content from 200+ publishers.