Purpose
TRANSLATE() replaces characters in char1 that match char2 with the corresponding characters in char3.
Syntax
TRANSLATE(char1,char2,char3)
Parameters
| Parameter | Description |
|---|---|
| char1 | The source character expression or variable. |
| char2 | The set of characters to be replaced in char1. |
| char3 | The set of characters to replace char2. |
Note
char1,char2, andchar3each can be of theCHAR,VARCHAR2,NCHAR,NVARCHAR2, orCLOBdata type.- If
char3contains more characters thanchar2, the extra characters at the end ofchar3are invalid. - If
char3contains fewer characters thanchar2, the extra characters at the end ofchar2are removed from the return value. - If
char3contains0characters, the function returnsNULL. - If
char2contains duplicate characters, the mapping in char3 corresponding to the first occurrence of the character is used for replacement. For example, ifchar2isaabandchar3is123, the function replaces allainchar1with1and allbwith3.
Return type
The return type is the same as the data type of char1.
Examples
The following example replaces a and e in the string OceanBase with A and E.
obclient> SELECT TRANSLATE('OceanBase','ae','AE') "TRANSLATE" FROM DUAL;
+-----------+
| TRANSLATE |
+-----------+
| OcEAnBAsE |
+-----------+
1 row in set
