Oracle CONVERT Function: A Simple Guide

The CONVERT function in Oracle SQL is a specialized function used to convert a character string from one character set to another (e.g., from WE8ISO8859P1 to US7ASCII).

Important Note: Oracle discourages the use of this function in modern applications. It is mainly intended for correcting data that was stored incorrectly. For most character set conversions, you should use RAW or BLOB data types and the UTL_RAW package.

What is the CONVERT Function in Oracle?

The CONVERT(char, dest_char_set, source_char_set) function takes a string and "translates" its underlying byte representation from a source character set to a destination character set.

If a character in the source set does not exist in the destination set, it is replaced with a "replacement character" (often a ? or a similar default).

CONVERT Function Syntax

The syntax for CONVERT is as follows:

CONVERT(char, dest_char_set, [source_char_set])

Let's break that down:

• char: The string or column you want to convert.
• dest_char_set: The name of the target character set you want to convert to (e.g., 'US7ASCII').
• [source_char_set] (Optional): The name of the original character set you are converting from (e.g., 'WE8ISO8859P1'). If omitted, this defaults to your database's character set.

Oracle CONVERT Function Examples

Here are two practical examples of how to use CONVERT.

Example 1: Converting from Latin-1 to ASCII using CONVERT

This example converts a string containing Latin-1 special characters (like Ä and Ê) into a 7-bit ASCII string. Since ASCII does not have these characters, they will be replaced.

Query:

SELECT 
  CONVERT('Ä Ê Í Õ Ø A B C', 'US7ASCII', 'WE8ISO8859P1') AS "Converted_String"
FROM DUAL;

Result: (Notice the non-ASCII characters were converted to ? or other replacement characters, while 'A', 'B', and 'C' were unchanged.)

Converted_String
-----------------
A E I ? ? A B C ?

Example 2: Finding a List of Valid Character Sets

Before you can use CONVERT, you need to know the name of the character set you are targeting. You can find a list of all valid character sets by querying the V$NLS_VALID_VALUES view.

Query:

SELECT value AS "Valid_Character_Sets"
FROM V$NLS_VALID_VALUES
WHERE parameter = 'CHARACTERSET';

Result: (This will return a long list of all character sets your database supports.)

Valid_Character_Sets
------------------------
US7ASCII
WE8ISO8859P1
WE8DEC
UTF8
AL32UTF8
...and many more