All Products
Search
Document Center

TRANSLATE

Last Updated: Jun 18, 2021

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, and c3 can be CHAR, VARCHAR2, NCHAR, NVARCHAR2, or CLOB.

  • If the length of c3 is greater than that of c2, the extra characters in c3 are invalid.

  • If the length of c3 is less than that of c2, extra characters in c2 are replaced with null. This indicates that extra characters are deleted.

  • If the length of c3 is 0, an empty string is returned.

  • If c2 contains 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      |
+------------------+