Oracle CONVERT Function Explained with Examples

The Oracle CONVERT function doesn’t do what you might expect it do, if you just look at its name. Learn what this function does and how to use it in this article.

Purpose of the Oracle CONVERT Function

The CONVERT function converts a string from one character set to another.

It doesn’t convert between data types, as you might have thought. If you want to convert between data types, you can try a few different functions: CAST, TO_DATE, TO_CHAR, or TO_NUMBER.

A character set is, well, a set of characters and how they are represented.

Some of the more common character sets are:

Character Set	Description
US7ASCII	US 7-bit ASCII character set
WE8ISO8859P1	ISO 8859-1 West European 8-bit character set
EE8MSWIN1250	Microsoft Windows East European Code Page 1250
WE8MSWIN1252	Microsoft Windows West European Code Page 1252
WE8EBCDIC1047	IBM West European EBCDIC Code Page 1047
JA16SJISTILDE	Japanese Shift-JIS Character Set, compatible with MS Code Page 932
ZHT16MSWIN950	Microsoft Windows Traditional Chinese Code Page 950
UTF8	Unicode 3.0 Universal character set CESU-8 encoding form
AL32UTF8	Unicode 5.0 Universal character set UTF-8 encoding form

You might have heard of UTF8 or ASCII before.

 

Syntax

The syntax of the CONVERT function is:

CONVERT ( input_char, dest_char_set, [source_char_set] )

 

Parameters

The parameters of the CONVERT function are:

• input_char (mandatory): This is the value to be converted. It can be any of CHAR, VARCHAR2, NCHAR, NVARCHAR2, CLOB, or NCLOB.
• dest_char_set (optional): This is the name of the character set that input_char is converted to.
• source_char_set (optional): This is the name of the character set that the input_char is stored in the database. The default value is the database character set.

Some additional notes about this function:

• The return data type for CHAR and VARCHAR2 parameters are VARCHAR2.
• The return data type for NCHAR and NVARCHAR2 parameters are NVARCHAR2.
• The return data type for CLOB is CLOB.
• The return data type for NCLOB is NCLOB.
• The source_char_set and dest_char_set can either be literal string (you type them in yourself), or a column in the database.

 

Oracle Discourages the Use of CONVERT

Oracle has advised that the CONVERT function should not be used in the current release of their database.

This is because the return value of this function is in a character data type (CHAR, NCHAR, or even CLOB and NCLOB). If the dest_char_set is not set to either the database character set or the national character set is not supported.

They have advised that the only use for this function should be to correct data that has been stored in the wrong character set.

More information about this as quoted by Oracle (https://docs.oracle.com/cd/B28359_01/server.111/b28286/functions027.htm):

Oracle discourages the use of the CONVERT function in the current Oracle Database release. The return value of CONVERT has a character datatype, so it should be either in the database character set or in the national character set, depending on the datatype. Any dest_char_set that is not one of these two character sets is unsupported. The charargument and the source_char_set have the same requirements. Therefore, the only practical use of the function is to correct data that has been stored in a wrong character set.

Values that are in neither the database nor the national character set should be processed and stored as RAW or BLOB. Procedures in the PL/SQL packages UTL_RAW and UTL_I18N—for example, UTL_RAW.CONVERT—allow limited processing of such values. Procedures accepting RAW argument in the packages UTL_FILE, UTL_TCP, UTL_HTTP, and UTL_SMTPcan be used to output the processed data.

Examples of the CONVERT Function

Here are some examples of the CONVERT function. I find that examples are the best way for me to learn about code, even with the explanation above.

 

Example 1

This example converts from one character set to another.

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

Result:

CONVERT_TEST

?? ?? ?? ?? ?? A B C D E

 

Example 2

This example converts from one character set to another.

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

Result:

CONVERT_TEST

Ä Ê Í Õ Ø A B C D E

 

Similar Functions

There aren’t really any functions similar to CONVERT, but some data-type conversion functions are:

• TO_CHAR – Converts an input value to a string.
• CAST – Converts an input value to another data type.

You can find a full list of Oracle SQL functions here.