Functions, 150 of 177

TRANSLATE

Syntax

translate::=

Text description of functions120.gif follows

Purpose

TRANSLATE returns char with all occurrences of each character in from_string replaced by its corresponding character in to_string. Characters in char that are not in from_string are not replaced. The argument from_string can contain more characters than to_string. In this case, the extra characters at the end of from_string have no corresponding characters in to_string. If these extra characters appear in char, then they are removed from the return value.

You cannot use an empty string for to_string to remove all characters in from_string from the return value. Oracle interprets the empty string as null, and if this function has a null argument, then it returns null.

Note:

This function does not support CLOB data directly. However, CLOBs can be passed in as arguments through implicit data conversion. Please refer to "Datatype Comparison Rules" for more information.

Examples

The following statement translates a license number. All letters 'ABC...Z' are translated to 'X' and all digits '012 . . . 9' are translated to '9':

SELECT TRANSLATE('2KRW229',
'0123456789ABCDEFGHIJKLMNOPQRSTUVWXYZ', 
'9999999999XXXXXXXXXXXXXXXXXXXXXXXXXX') "License"
     FROM DUAL;
 
License
--------
9XXX999

The following statement returns a license number with the characters removed and the digits remaining:

SELECT TRANSLATE('2KRW229',
   '0123456789ABCDEFGHIJKLMNOPQRSTUVWXYZ', '0123456789') 
   "Translate example"
     FROM DUAL;
 
Translate example
-----------------
2229