Oracle TO_CHAR (Number) Function: A Simple Guide to Formatting Numbers

The TO_CHAR function for numbers is one of the most important formatting functions in Oracle SQL. Its job is to take a NUMBER (or BINARY_FLOAT, BINARY_DOUBLE) and convert it into a human-readable text string (VARCHAR2) based on a "format model" you provide.

This function is essential for creating reports, as it gives you complete control over how numbers are displayed. You can add currency symbols (like $), commas (group separators), trailing minus signs, and control the number of decimal places.

What is the TO_CHAR (Number) Function in Oracle?

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

This is the opposite of the TO_NUMBER function. It's not used for math, but for presentation.

TO_CHAR (Number) Function Syntax

The syntax for TO_CHAR (Number) is:

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

Let's break that down:

• n: The number (or numeric column) you want to format.
• [fmt] (Optional): A text string "format model" that defines how the number should look. If you omit this, Oracle just converts the number to a simple string.
• [nlsparam] (Optional): A string to specify NLS (National Language Support) parameters, like a specific currency symbol or decimal separator.

Common Format Models (fmt)

• 9: Represents a significant digit.
• 0: Represents a digit, but will display a leading 0 if the number is too small.
• L: Displays the local currency symbol (e.g., $).
• G: Displays the group separator (e.g., a comma ,).
• D: Displays the decimal separator (e.g., a period .).
• MI: Displays a negative sign on the right (trailing) for negative numbers.
• PR: Encloses negative numbers in angle brackets (< >).

Oracle TO_CHAR (Number) Function Examples

Here are two practical examples of how to use TO_CHAR for numbers.

Example 1: Basic Conversion (No Format) using TO_CHAR

This example shows the simplest use, without a format model. It just converts the employee_id number into a VARCHAR2 string. This is useful if you need to join a number to a string.

Query:

-- This query converts the numeric employee_id to a string
-- so it can be combined with the 'ID: ' text.
SELECT 
  'ID: ' || TO_CHAR(employee_id) AS "Employee_ID_Text"
FROM   
  employees
WHERE  
  employee_id IN (111, 112);

Result:

Employee_ID_Text
----------------
ID: 111
ID: 112

Example 2: Formatting a Number as Currency using TO_CHAR

This is the most common use case. Let's take a negative number and format it as currency, with commas, two decimal places, and a trailing minus sign.

Query:

-- L = Local Currency Symbol ($)
-- 99G999 = A number up to 99,999 with a Group separator (,)
-- D99 = A Decimal point with two digits
-- MI = A Trailing Minus sign for negative numbers
SELECT 
  TO_CHAR(-10000, 'L99G999D99MI') AS "Amount"
FROM DUAL;

Result:

Amount
--------------
  $10,000.00-

Notice how the format model L99G999D99MI perfectly formatted the raw number -10000 into a clean, readable currency string.