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)
In this tutorial, you have learned how to use the Oracle CONVERT()
function to convert a string from a character set to another.