TRANSLATE

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

Note:

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

Return value

Text.

Syntax

TRANSLATE ( exp from_string to_string)

Parameters

exp: A text expression in which you want to replace characters.
from_string: A text expression that is the characters you want to replace.
to_string: A text expression that is the characters 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 interpreted as a null argument.

Examples

OLADM1998 Example 8-151 Replacing several 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 which 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' '* \'' '__')
SQL_Plus_Users_Guide