The TRANSLATE function replaces specified characters in the value of a character expression with new characters. A multibyte character, such as a Chinese character and a full-width character, is calculated as one character.
Syntax
TRANSLATE(c1,c2,c3)
Parameters
| Parameter | Description |
|---|---|
| c1 | The character or the variable that you want to replace. |
| c2 | The original character set to be queried. |
| c3 | The new character set for replacement. Characters in a sequence in c2 are replaced with characters in the corresponding sequence in c3. |
Note
The data types of
c1,c2, andc3can beCHAR,VARCHAR2,NCHAR,NVARCHAR2, orCLOB.If the length of
c3is greater than that ofc2, the extra characters inc3are invalid.If the length of
c3is less than that ofc2, extra characters inc2are replaced with null. This indicates that extra characters are deleted.If the length of
c3is 0, an empty string is returned.If
c2contains duplicate characters, the character in the position of the first occurrence is replaced.
Return type
The characters of the CHAR, VARCHAR2, NCHAR, NVARCHAR2, or CLOB type are returned.
Examples
Execute the following statement:
SELECT TRANSLATE('he love you','he','i') FROM DUAL;
The following query result is returned:
+------------------+
| TRANSLATE |
+------------------+
| i love you |
+------------------+