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_inIN VARCHAR2,search_setIN VARCHAR2,replace_setVARCHAR2) 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) --> NULLYou 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. ...
Become an O’Reilly member and get unlimited access to this title plus top books and audiobooks from O’Reilly and nearly 200 top publishers, thousands of courses curated by job role, 150+ live events each month,
and much more.
Read now
Unlock full access