TRANSLATE ... USING

Syntax

translate_using::=

Text description of translate_using

Purpose

TRANSLATE ... USING converts text into the character set specified for conversions between the database character set and the national character set.

The text argument is the expression to be converted.

• Specifying the USING CHAR_CS argument converts text into the database character set. The output datatype is VARCHAR2.

• Specifying the USING NCHAR_CS argument converts text into the national character set. The output datatype is NVARCHAR2.

This function is similar to the Oracle CONVERT function, but must be used instead of CONVERT if either the input or the output datatype is being used as NCHAR or NVARCHAR2. If the input contains UCS2 codepoints or backslash characters (\), use the UNISTR function.

See Also: CONVERT and UNISTR

Examples

The hypothetical examples below use the following table and table values:

CREATE TABLE t1 (char_col  CHAR(20),
                 nchar_col nchar(20));
INSERT INTO t1 
  VALUES ('Hi', N'Bye');
SELECT * FROM t1;

CHAR_COL     NCHAR_COL
--------     ---------
Hi           Bye

UPDATE t1 SET
  nchar_col = TRANSLATE(char_col USING NCHAR_CS);
UPDATE t1 SET
  char_col = TRANSLATE(nchar_col USING CHAR_CS);
SELECT * FROM t1;

CHAR_COL     NCHAR_COL
--------     ---------
Hi           Hi

UPDATE t1 SET
  nchar_col = TRANSLATE('deo' USING NCHAR_CS);
UPDATE t1 SET
  char_col = TRANSLATE(N'deo' USING CHAR_CS);
SELECT * FROM t1;

CHAR_COL     NCHAR_COL
--------     ---------
deo          deo