Oracle TO_NCHAR (number) Function: A Simple Guide

The TO_NCHAR (number) function in Oracle SQL is a conversion function for formatting numbers. It works almost identically to the standard TO_CHAR(number) function, with one key difference: it returns the formatted string in the national character set (as an NVARCHAR2 data type).

You use this function when you need to display a formatted number (like currency or with commas) in an application or table that specifically requires the national character set (often used for Unicode).

What is the TO_NCHAR (number) Function in Oracle?

The TO_NCHAR(n, 'format') function "translates" a numeric value (like 12345.6) into a formatted NVARCHAR2 string (like '$12,345.60').

• TO_CHAR(number) returns a VARCHAR2 string (database character set).
• TO_NCHAR(number) returns an NVARCHAR2 string (national character set).

Like TO_CHAR, its power comes from the format model (fmt), which is a string of codes that define exactly how the number should be presented (e.g., with currency symbols, commas, and decimal points).

TO_NCHAR (number) Function Syntax

The syntax for TO_NCHAR (number) is:

TO_NCHAR(n [, fmt] [, 'nlsparam'])

Let's break that down:

• n: The number (or numeric column) you want to format (e.g., salary).
• [fmt] (Optional): A text string "format model" that defines how the number should look (e.g., 'L99G999D99').
• [nlsparam] (Optional): A string to specify NLS parameters, like a specific currency symbol or decimal separator.

Oracle TO_NCHAR (number) Function Examples

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

Example 1: Comparing TO_NCHAR and TO_CHAR using DUMP

This example shows the internal difference between TO_NCHAR and TO_CHAR. We use the DUMP function, which reveals the data type. TO_NCHAR returns Typ=1 (NVARCHAR2), while TO_CHAR returns Typ=96 (VARCHAR2).

Query:

SELECT
  DUMP(TO_NCHAR(105)) AS "NCHAR_Dump",
  DUMP(TO_CHAR(105)) AS "CHAR_Dump"
FROM DUAL;

Result: (Note the Typ=1 for TO_NCHAR and Typ=96 for TO_CHAR)

NCHAR_Dump                CHAR_Dump
------------------------- --------------------
Typ=1 Len=6: 0,49,0,48,0,53 Typ=96 Len=3: 49,48,53

Example 2: Formatting a Number with TO_NCHAR

This example shows that TO_NCHAR uses the same powerful format models as TO_CHAR. Here, we format a number as currency.

Query:

SELECT
  TO_NCHAR(12345.67, 'L99G999D99') AS "Formatted_NCHAR"
FROM DUAL;

Result: (The output is a formatted NVARCHAR2 string)

Formatted_NCHAR
---------------
     $12,345.67