The Oracle CONVERT() function converts a string from one character set to another.
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:
Code language: SQL (Structured Query Language) (sql)CONVERT(string_expression,to_data_set[,from_data_set]);
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 character set which 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:
Code language: SQL (Structured Query Language) (sql)'ABC'
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:
Code language: SQL (Structured Query Language) (sql)'A E I'
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)In this tutorial, you have learned how to use the Oracle CONVERT() function to convert a string from a character set to another.