Oracle CONVERT() Function

Oracle CONVERT() is a built-in function that converts a given string from one character set to another.

Oracle `CONVERT()` Syntax

Here is the syntax for the Oracle CONVERT() function:

CONVERT(char, dest_char_set[, source_char_set ])

Parameters

char: Required. It is the value to be converted. It can be of data types such as CHAR, VARCHAR2, NCHAR, NVARCHAR2, CLOB, or NCLOB.
dest_char_set: Required. It is the name of the character set to which char is converted.
source_char_set: Optional. It is the name of the character set in which char is stored in the database. The default value is the database character set.

Both the destination character set dest_char_set and the source character set source_char_set parameters can be literal values or columns containing character set names.

In order to achieve complete correspondence in character conversion, the destination character set must include representations of all the characters defined in the source character set. If a character does not exist in the destination character set, a replacement character will be used. The replacement character can be defined as part of the character set definition.

Return Value

The Oracle CONVERT() function converts a given string from one character set to another and returns the converted result.

If the type of char is CHAR and VARCHAR2, the return type is VARCHAR2. If the type of char is NCHAR and NVARCHAR2, the return type is NVARCHAR2. If the type of char is CLOB, the return type is CLOB. If the type of char is NCLOB, the return type is NCLOB.

If any of the parameters are NULL, CONVERT() returns NULL.

Oracle `CONVERT()` Examples

Here are several examples that demonstrate the usage of Oracle’s CONVERT() function.

Basic Usage

The following statement converts a Latin-1 string to ASCII:

SELECT
    CONVERT('Ä Ê Í Õ Ø A B C D E ', 'US7ASCII', 'WE8ISO8859P1')
FROM dual;

输出：

CONVERT('ÄÊÍÕØABCDE'
---------------------
A E I ? ? A B C D E ?

NULL Parameters

If any parameter is NULL, CONVERT() will return NULL.

SET NULL 'NULL';
SELECT
    CONVERT(NULL, NULL),
    CONVERT(NULL, NULL, NULL)
FROM dual;

输出：

CONVERT(NULL,NULL)    CONVERT(NULL,NULL,NULL)
_____________________ __________________________
NULL                  NULL

In this example, we use the SET NULL 'NULL'; statement to display NULL values as the string 'NULL'.

Conclusion

Oracle’s CONVERT() is a built-in function that converts a given string from one character set to another.

Oracle CONVERT() Syntax