Oracle TRANSLATE(using) Function

Oracle TRANSLATE(using) is a built-in function that converts the given string between the database character set and the national character set.

The Oracle TRANSLATE(using) function is mainly provided for ANSI compatibility, and Oracle recommends using the TO_CHAR and TO_NCHAR functions for character set conversions.

Oracle `TRANSLATE(using)` Syntax

Here is the syntax for the Oracle TRANSLATE(using) function:

TRANSLATE(char USING { CHAR_CS | NCHAR_CS })

Parameters

char: Required. The string to be converted.

Return Value

The Oracle TRANSLATE(using) function converts the given string between the database character set and the national character set and returns the converted string.

If any of the parameters is NULL, TRANSLATE(using) returns NULL.

Oracle `TRANSLATE(using)` Examples

Here are some examples that demonstrate the usage of the Oracle TRANSLATE(using) function.

Basic Usage

SELECT
    TRANSLATE('A' USING CHAR_CS) CHAR_CS,
    TRANSLATE('A' USING NCHAR_CS) NCHAR_CS
FROM dual;

Output:

CHAR_CS    NCHAR_CS
__________ ___________
A          A

NULL Parameter

If any of the parameters is NULL, TRANSLATE() returns NULL.

SET NULL 'NULL';
SELECT
    TRANSLATE(NULL USING CHAR_CS) Result
FROM dual;

Output:

RESULT
_________
NULL

In this example, we use the SET NULL 'NULL'; statement to display NULL values as the string 'NULL'.

Conclusion

The Oracle TRANSLATE(using) function is a built-in function that converts the given string between the database character set and the national character set.

Oracle TRANSLATE(using) Syntax