Oracle PL/SQL Programming, Third Edition

Name

TRANSLATE...USING

Synopsis

This function exists to provide ANSI compatibility, and translates character data between character sets. Because TRANSLATE . . . USING is so unusual, we provide the syntax for using it, rather than its specification:

TRANSLATE(text USING {CHAR_CS | NCHAR_CS}

where:

text

Is the text you wish to translate. This may be a character variable, an expression, or a literal text string.

CHAR_CS

Specifies that the input text should be converted into the database character set.

NCHAR_CS

Specifies that the input text should be converted into the national character set.

The output datatype will be either VARCHAR2 or NVARCHAR2, depending on whether you are converting to the database or the national character set, respectively.

Following is a simple example showing the use of this function:

DECLARE 
   a VARCHAR2(30) := 'Corner? What corner?';
   b NVARCHAR2(30);
BEGIN
   b := TRANSLATE(a USING NCHAR_CS);
END;

In this example, the characters in the string are represented using the database character set. TRANSLATE . . . USING converts those characters into their national character set equivalents.

Tip

In Oracle9i you can simply assign a VARCHAR2 to an NVARCHAR2, (and vice versa), and Oracle will handle the conversion implicitly. If you wish to make such a conversion explicit, you can use TO_CHAR and TO_NCHAR to convert text to database and national character sets, respectively. Oracle recommends the use of TO_CHAR and TO_NCHAR over TRANSLATE...USING, because those functions support ...