TRANSLATE(… USING) Function in Oracle

by

In Oracle, the TRANSLATE(...USING) function converts its argument into the character set specified for conversions between the database character set and the national character set.

This function is not to be confused with the TRANSLATE() function, which allows you to make several single-character, one-to-one substitutions in one operation.

The TRANSLATE(...USING) function is supported in Oracle primarily for ANSI compatibility. Oracle recommends that we use the TO_CHAR() and TO_NCHAR() functions instead.

Syntax

The syntax goes like this:

TRANSLATE ( char USING
          { CHAR_CS | NCHAR_CS }
          )

Where char is the expression to be converted, and:

  • Specifying the USING CHAR_CS argument converts char into the database character set. The output data type is VARCHAR2.
  • Specifying the USING NCHAR_CS argument converts char into the national character set. The output data type is NVARCHAR2.

Example

Here’s a basic example:

SELECT 
    TRANSLATE('Cat' USING CHAR_CS) AS CHAR_CS,
    TRANSLATE('Cat' USING NCHAR_CS) AS NCHAR_CS
FROM DUAL;

Result:

   CHAR_CS    NCHAR_CS 
__________ ___________ 
Cat        Cat

To demonstrate the difference, here’s what happens when we pass it to the DUMP() function:

SELECT 
    DUMP(TRANSLATE('Cat' USING CHAR_CS), 17) AS CHAR_CS,
    DUMP(TRANSLATE('Cat' USING NCHAR_CS), 17) AS NCHAR_CS
FROM DUAL;

Result:

              CHAR_CS                       NCHAR_CS 
_____________________ ______________________________ 
Typ=1 Len=3: C,a,t    Typ=1 Len=6: ^@,C,^@,a,^@,t

Null Arguments

Passing null as the argument returns null:

SET NULL 'null';
SELECT 
    TRANSLATE(null USING CHAR_CS) AS Result
FROM DUAL;

Result:

null

By default, SQLcl and SQL*Plus return a blank space whenever null occurs as a result of a SQL SELECT statement.

However, you can use SET NULL to specify a different string to be returned. Here I specified that the string null should be returned.

Missing Argument

Calling the function with without passing the char argument results in an error:

SELECT TRANSLATE(USING CHAR_CS)
FROM DUAL;

Result:

Error starting at line : 1 in command -
SELECT TRANSLATE(USING CHAR_CS)
FROM DUAL
Error at Command Line : 1 Column : 8
Error report -
SQL Error: ORA-00909: invalid number of arguments
00909. 00000 -  "invalid number of arguments"
*Cause:    
*Action: