Oracle TO_NCHAR (character) Function: A Simple Guide

The TO_NCHAR (character) function in Oracle SQL is a conversion function. Its primary job is to convert various character data types (like CHAR, VARCHAR2, or CLOB) into the national character set.

The function always returns a value of type NVARCHAR2.

This is the opposite of TO_CHAR (character), which converts from the national character set. You use TO_NCHAR when you need to store or process standard database text within the national character set (which is often used for Unicode).

What is the TO_NCHAR (character) Function in Oracle?

The TO_NCHAR(char) function takes a character string (CHAR, VARCHAR2, CLOB, or NCLOB) and returns its data as an NVARCHAR2 string.

This is most commonly used when you need to:

• Copy data from a VARCHAR2 column into an NVARCHAR2 column.
• Pass a standard VARCHAR2 string to a function that only accepts a national character set type.

TO_NCHAR (character) Function Syntax

The syntax for TO_NCHAR (character) is very simple:

TO_NCHAR(char)

Let's break that down:

• char: The value or column you want to convert. This can be CHAR, VARCHAR2, CLOB, or NCLOB.

Oracle TO_NCHAR (character) Function Examples

Here are two practical examples of how to use TO_NCHAR (character).

Example 1: Converting a VARCHAR2 string with TO_NCHAR

This example shows how to convert a simple VARCHAR2 string literal into the national character set. We use the DUMP function to show the internal data type change (Typ=96 for VARCHAR2 vs. Typ=1 for NVARCHAR2).

Query:

SELECT
  DUMP('Hello') AS "VARCHAR2_Dump",
  DUMP(TO_NCHAR('Hello')) AS "NVARCHAR2_Dump"
FROM DUAL;

Result: (The exact byte values will vary, but note the change in Typ and Len)

VARCHAR2_Dump             NVARCHAR2_Dump
------------------------- -----------------------------------
Typ=96 Len=5: 72,101,108,108,111 Typ=1 Len=10: 0,72,0,101,0,108,0,108,0,111

The Typ=1 and different length in the NVARCHAR2_Dump confirm the conversion.

Example 2: Converting a Table Column with TO_NCHAR

This is a more common use case. You have a standard VARCHAR2 column (cust_last_name) and you need to select its data, but as an NVARCHAR2 data type.

Query:

-- This query fetches the last name from the VARCHAR2 column
-- and converts it to the national character set (NVARCHAR2)
SELECT
  TO_NCHAR(cust_last_name)
FROM
  customers
WHERE
  customer_id = 103;

Result:

TO_NCHAR(CUST_LAST_NAME)
--------------------------------------------------
Taylor