Oracle TRANSLATE USING Function: A Simple Guide

The TRANSLATE USING function in Oracle SQL is a specialized function used to convert a string from one character set to another.

Specifically, it converts a string to either the database's main character set or its national character set (NCHAR).

Note: This function is mainly supported for ANSI compatibility. Oracle strongly recommends using the more flexible TO_CHAR and TO_NCHAR functions for character set conversions.

What is the TRANSLATE USING Function?

This function's only job is to change the character set of a string. It's different from the other TRANSLATE function, which replaces individual characters.

This is useful for:

• Converting NVARCHAR2 data (national character set) into VARCHAR2 (database character set).
• Converting VARCHAR2 data into NVARCHAR2 for storage in an NCHAR or NVARCHAR2 column.
• Ensuring data is in the correct character set before an operation.

TRANSLATE USING Function Syntax

The syntax is different from the regular TRANSLATE function:

TRANSLATE(char USING { CHAR_CS | NCHAR_CS })

Let's break that down:

• char: The string or column you want to convert.
• USING CHAR_CS: This converts the string to the main database character set (and returns a VARCHAR2).
• USING NCHAR_CS: This converts the string to the national character set (and returns an NVARCHAR2).

Oracle TRANSLATE USING Examples

Here are two practical examples of how to use TRANSLATE USING.

Example 1: Convert to the National Character Set (NCHAR_CS) with TRANSLATE USING

Imagine you have a VARCHAR2 value (like a product name) that you need to insert into an NVARCHAR2 column. You can use TRANSLATE USING NCHAR_CS to ensure it's correctly converted.

Query:

-- This converts the standard VARCHAR2 string 'Hello' 
-- into the national character set (NVARCHAR2).
SELECT 
  TRANSLATE('Hello' USING NCHAR_CS) AS "NCHAR_String"
FROM DUAL;

Result: (The output will look like 'Hello', but its data type will now be NVARCHAR2.)

Example 2: Convert to the Database Character Set (CHAR_CS) with TRANSLATE USING

This is a more common scenario. Let's say you have a table product_descriptions with a column translated_name that is an NVARCHAR2 type. You want to copy this data into a standard VARCHAR2 column.

Query:

-- This assumes 'translated_name' is an NVARCHAR2 column
-- and converts its content to the database character set (VARCHAR2).
SELECT 
  TRANSLATE(translated_name USING CHAR_CS) AS "Standard_Text"
FROM product_descriptions
WHERE product_id = 3501;

Result: (The query will return the translated_name data, but its data type will be converted to VARCHAR2.)