Language Support   «Prev  Next»

Lesson 6Character Set Conversion
ObjectiveUnderstand how Oracle converts between character sets.

Character Set Conversion

Your Oracle database also includes a number of ways to convert data from one national character set to another.

Conversion functions

If you wish to convert data in a SQL statement from one character set to another, you use the CONVERT function. The syntax for the function is 

CONVERT(char, dest_char_set, [src_char_set])

where char is the character string to be converted, dest_char_set is the destination character set, and src_char_set is an optional parameter for the source character set. If this parameter is not included, Oracle assumes that the default character set for the server is the source character set.
You can also use the TRANSLATE USING function to translate values into either the database character set or the national character set for the database. The syntax for the function is 

TRANSLATE text USING CHAR_CS/NCHAR_CS

where text is the character string you wish to translate into a different character set;
  1. CHAR_CS indicates that you want to convert the string to the database character set, with an output type of VARCHAR2; and
  2. NCHAR_CS indicates that you want to convert the string to the national character set, with an output type of NVARCHAR2.

Other Conversion Functions

There are several other conversion functions you may use for specific types of conversions:
  1. The standard TO_CHAR, TO_DATE, and TO_NUMBER functions can have an optional parameter specified to indicate that the source is in a particular national character set.
  2. The NLS_UPPER, NLS_LOWER, and NLS_INITCAP functions take into account the fact that certain national character sets have different rules for uppercase, lowercase, and initial capitalization. These functions take these differences into account when you specify an NLS_SORT parameter.
  3. NLS_SORT is used when you want to compare values based on their order in the specified national language set. Normally, comparisons in a WHERE clause are made with binary values. If your national language was German and you wanted to compare two values based on their German sort order, you would use the syntax WHERE NLS_SORT(value1) > NLS_SORT(value2).

The following SlideShow shows how the conversion process with NLS_SORT would operate:
1) Sort Conversion 1 2) Sort Conversion 2 3) Sort Conversion 3 4) Sort Conversion 4
  1. With a normal WHERE clause, the statement returns values based on the binary order of the data
  2. For this German data, the statement would select the indicated rows.
  3. With the NLS_SORT parameter, Oracle knows to select the data based on the German sort order.
  4. which returns the following data

Character Set Conversion
The next lesson is the module conclusion.