Oracle CONVERT

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

Note that the CONVERT() function is often used to correct data stored in the database with a wrong character set.

Syntax #

The following shows the syntax of the Oracle CONVERT() function:

CONVERT(string_expression,to_data_set[,from_data_set]);Code language: SQL (Structured Query Language) (sql)

Arguments #

The Oracle CONVERT() function accepts three arguments:

1) string_expression

is the string whose character set should be converted.

2) to_data_set

is the name of the character set to which the string_expression is converted to:

3) from_data_set

is the name of the character set that is used to store the string_expression in the database. This argument is optional and its default value is the database character set.

Return value #

The CONVERT() function returns a string in the converted character set.

Examples #

The following example converts a string from ANSI to UTF8:

SELECT
  CONVERT( 'ABC', 'utf8', 'us7ascii' )
FROM
  dual;
Code language: SQL (Structured Query Language) (sql)

Here is the result:

'ABC'Code language: SQL (Structured Query Language) (sql)

The following statement illustrates how to convert the character set of a string from Latin-1 to ASCII.

SELECT
  CONVERT( 'Ä Ê Í', 'US7ASCII', 'WE8ISO8859P1' )
FROM
  DUAL;Code language: SQL (Structured Query Language) (sql)

The result is:

'A E I'
Code language: SQL (Structured Query Language) (sql)

Note that the result is the same as converting the string from the WE8ISO8859P1 character set to the US7ASCII character set.

Remarks #

The following table shows the common character sets:

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

To get all the valid character sets, you query them from the V$NLS_VALID_VALUES view as follows:

SELECT
  value
FROM
  V$NLS_VALID_VALUES
WHERE
  parameter = 'CHARACTERSET' AND ISDEPRECATED = 'FALSE'
ORDER BY
  value;Code language: SQL (Structured Query Language) (sql)

Summary #

Use the Oracle CONVERT() function to convert a string from a character set to another.

Was this tutorial helpful?