Oracle PL/SQL Programming, Third Edition

Name

TRANSLATE

Synopsis

The TRANSLATE function is a variation on REPLACE. REPLACE replaces every instance of a set of characters with another set of characters; that is, REPLACE works with entire words or patterns. TRANSLATE replaces single characters at a time, translating the nth character in the search set to the nth character in the replacement set. The specification of the TRANSLATE function is as follows:

FUNCTION TRANSLATE 
   (string_in IN VARCHAR2, 
    search_set IN VARCHAR2, 
    replace_set VARCHAR2)
RETURN VARCHAR2

where string_in is the string in which characters are to be translated, search_set is the set of characters to be translated (if found), and replace_set is the set of characters that will be placed in the string. Unlike REPLACE, where the last argument can be left off, you must include all three arguments when you use TRANSLATE. Any of the three arguments may, however, be NULL, in which case TRANSLATE always returns NULL.

Here are some examples of TRANSLATE:

TRANSLATE ('abcd', 'ab', '12') --> '12cd'

TRANSLATE ('12345', '15', 'xx') --> 'x234x'

TRANSLATE ('grumpy old possum', 'uot', '%$*') -->   'gr%mpy $ld p$ss%m'

TRANSLATE ('my language needs the letter e', 'egms', 'X') 
 --> 
   'y lanuaX nXXd thX lXttXr X';

TRANSLATE ('please go away', 'a', NULL) --> NULL

You can deduce a number of the usage rules for TRANSLATE from the above examples, but we’ll spell them out here:

• If the search set contains a character not found in the string, then no translation is performed for that character. ...