Oracle Tutorial

  • Home
  • Start Here
  • Basics
  • Advanced
    • Oracle View
    • Oracle Index
    • Oracle Synonym
    • Oracle Sequence
    • Oracle Administration
  • PL/SQL
  • Functions
    • Aggregate Functions
    • Analytic Functions
    • Comparison Functions
    • Date Functions
    • String Functions
  • API
    • Python Oracle
Home / Oracle String Functions / Oracle CONVERT

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]);

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;

Here is the result:

'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;

The result is:

'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 SetDescription
AL32UTF8Unicode 5.0 Universal character set UTF-8 encoding form
EE8MSWIN1250Microsoft Windows East European Code Page 1250
JA16SJISTILDEJapanese Shift-JIS Character Set, compatible with MS Code Page 932
US7ASCIIUS 7-bit ASCII character set
UTF8Unicode 3.0 Universal character set CESU-8 encoding form
WE8EBCDIC1047IBM West European EBCDIC Code Page 1047
WE8ISO8859P1ISO 8859-1 West European 8-bit character set
WE8MSWIN1252Microsoft Windows West European Code Page 1252
ZHT16MSWIN950Microsoft 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;

In this tutorial, you have learned how to use the Oracle CONVERT() function to convert a string from a character set to another.

  • Was this tutorial helpful?
  • YesNo
Previous Oracle CONCAT
Next Oracle DUMP

Oracle Functions

  • Aggregate Functions
  • Analytic Functions
  • Comparison Functions
  • Date Functions
  • String Functions

String Functions

  • ASCII
  • CHR
  • CONCAT
  • CONVERT
  • DUMP
  • INSTR
  • INITCAP
  • LENGTH
  • LOWER
  • LPAD
  • LTRIM
  • REPLACE
  • REGEXP_COUNT
  • REGEXP_INSTR
  • REGEXP_LIKE
  • REGEXP_REPLACE
  • REGEXP_SUBSTR
  • RPAD
  • RTRIM
  • SOUNDEX
  • SUBSTR
  • TRANSLATE
  • TRIM
  • UPPER

About Oracle Tutorial

OracleTututorial.com website provides Developers and Database Administrators with the updated Oracle tutorials, scripts, and tips.

Search

Recent Tutorials

  • Oracle Implicit Statement Results
  • Calling PL/SQL Stored Functions in Python
  • Calling PL/SQL Procedures in Python
  • Managing Transaction in Python
  • Deleting Data From Oracle Database in Python

Site Links

  • Oracle Books
  • About
  • Contact
  • Privacy Policy
  • Terms of Use

Copyright © 2021 Oracle Tutorial. All Rights Reserved.