The TRANSLATE function replaces all occurrences of each character of one string with the corresponding character in another string.


TRANSLATE provides functionality related to that provided by the REPLCHARS function. REPLCHARS lets you substitute a single string for another single string, as well as remove character strings. TRANSLATE lets you make several single-character, one-to-one substitutions in one operation.

Return value



TRANSLATE ( exp from_string to_string)



A text expression in which you want to replace characters.


A text expression that is the characters you want to replace.


A text expression that is the characters that you want to use for replacement in the order of from_string. When you include fewer characters in this argument than are in from_string, the function removes the extra characters in from_string from the return value. Note, however, that to remove all characters in from_string, you cannot specify an empty string for to_string as an empty string is interrpreted as a null argument.


Example 8-149 Replacing a Number of Characters Using TRANSLATE

The following statement translates a book title into a string that could be used (for example) as a filename. The from_string contains three characters: an asterisk, a space, asterisk, and apostrophe (with a backslash as the escape character). The to_string contains only two underscores. This leaves the third character in the from_string without a corresponding replacement, so the return value does not contain any apostrophes.

SHOW TRANSLATE ('SQL*Plus User\'s Guide' '* \'' '__')