In Oracle Database, the TO_CHAR(number) function converts a number to a VARCHAR2 value in the format specified by the format argument.

Syntax

The syntax goes like this:

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

Where:

• n can be of type NUMBER, BINARY_FLOAT, or BINARY_DOUBLE.
• fmt is an optional format model that specifies how the result should be formatted. See the valid number format elements that can be used to construct your format model.
• 'nlsparam' is an optional argument that specifies how various characters are returned. You can use it to specify the decimal character and the group separator, the local currency symbol, and the international currency symbol. If this argument is omitted, then the default values for the current session are used.

Example

Here’s an example to demonstrate how the function works:

SELECT TO_CHAR(12345, '99G999') AS Result
FROM DUAL;

Result:

    RESULT 
__________ 
 12,345   

The first argument is the actual number that we’re formatting, and the second argument is the format model. The format model specifies how the number is formatted.

In this case, our format model formats the number with a group separator in the appropriate position. Each numeric digit is represented by 9, and the group separator is represented by G.

Here’s what happens if we change the format model slightly:

SELECT TO_CHAR(12345, '099G999') AS Result
FROM DUAL;

Result:

     RESULT 
___________ 
 012,345    

In this case, we started the format model with a 0 character. This returns a leading zero wherever applicable. The 9 character doesn’t return leading zeros.

In fact, we could replace all the 9s with 0s if we want. Here’s a comparison that shows how the results can differ quite considerably, depending on which format element you use:

SELECT 
    TO_CHAR(12, '000G000') AS "000G000",
    TO_CHAR(12, '999G999') AS "999G999"
FROM DUAL;

Result:

    000G000     999G999 
___________ ___________ 
 000,012          12    

Currency Symbols

You can use the L format element to return the local currency symbol.

Example:

SELECT TO_CHAR(12345, 'L99G999D99') AS Result
FROM DUAL;

Result:

                 RESULT 
_______________________ 
          $12,345.00   

This example uses a format model that outputs the result using the local currency symbol (represented by L), a group separator (represented by G), a decimal character (represented by D), and of course, each numeric digit (represented by 9).

The local currency symbol is determined by the current value of the NLS_CURRENCY parameter. It’s true that we could have used a dollar sign ($) for the currency symbol, but that assumes that the currency is denominated in dollars. There are many other possible currencies around the world, and the L format element is able to dynamically return the local currency symbol for the user’s session.

See How to Check the Values of the NLS Parameters if you need to check them.

See this Full List of Number Format Elements in Oracle for a list of format elements that can be used to format numbers with this function.

Default Format

If you omit the fmt argument, the number is converted to a VARCHAR2 value exactly long enough to hold its significant digits.

Here’s an example of a converting a number without specifying the format:

SELECT TO_CHAR(525.45)
FROM DUAL;

Result:

525.45

The 'nlsparam' Argument

The 'nlsparam' argument can be used to specify the decimal character and the group separator, the local currency symbol, and the international currency symbol.

It takes the following form:

'NLS_NUMERIC_CHARACTERS = ''dg''
   NLS_CURRENCY = ''text''
   NLS_ISO_CURRENCY = territory '

Example:

SELECT 
    TO_CHAR(
        1234.56, 
        'L99G999D99',
        'NLS_NUMERIC_CHARACTERS = '',.''
        NLS_CURRENCY = ''€''
        NLS_ISO_CURRENCY = Germany'
    )
FROM DUAL;

Result:

         €1.234,56

Here it is again, but this time I replace L with C in the format model:

SELECT 
    TO_CHAR(
        1234.56, 
        'C99G999D99',
        'NLS_NUMERIC_CHARACTERS = '',.''
        NLS_CURRENCY = ''€''
        NLS_ISO_CURRENCY = Germany'
    )
FROM DUAL;

Result:

      EUR1.234,56

The C returns the ISO currency symbol, which in this case is EUR.

How to Suppress the Padding

You may have noticed that some of the examples have padding applied to the left of the results. Such padding can be removed with the fm format modifier.

Example:

SELECT 
    TO_CHAR(
        1234.56, 
        'fmL99G999D99',
        'NLS_NUMERIC_CHARACTERS = '',.''
        NLS_CURRENCY = ''€''
        NLS_ISO_CURRENCY = Germany'
    )
FROM DUAL;

Result:

€1.234,56

Null Arguments

Passing null results in null:

SET NULL 'null';
SELECT TO_CHAR(null)
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 without passing any arguments, results in an error:

SELECT TO_CHAR()
FROM DUAL;

Result:

SQL Error: ORA-00938: not enough arguments for function
00938. 00000 -  "not enough arguments for function"

Passing too many arguments also results in an error:

SELECT TO_CHAR(123, '99', 'NLS_ISO_CURRENCY = GERMANY', 'oops!' )
FROM DUAL;

Result:

SQL Error: ORA-00939: too many arguments for function
00939. 00000 -  "too many arguments for function"