Oracle TO_CHAR(number) Function

Oracle TO_CHAR(number) is a built-in function that converts a given numeric value to a value of the VARCHAR2 data type using an optional format parameter.

Oracle `TO_CHAR(number)` Syntax

Here is the syntax for the Oracle TO_CHAR(number) function:

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

Parameters

n

Required. The numeric value to convert.

fmt

Optional. A formatting string, such as L99G999D99MI.

nlsparam

Optional. The nlsparam parameter specifies the characters returned by numeric format elements:

Decimal character
Group separator
Local currency symbol
International currency symbol

This parameter can take the following format:

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

The characters d and g represent the decimal character and group separator, respectively. They must be different single-byte characters. In quoted strings, two single quotes must surround the parameter value. Ten characters are available for the currency symbol.

If 'nlsparam' or any parameter is omitted, this function uses your session’s default parameter values.

Return Value

The Oracle TO_CHAR(number) function returns a value of the VARCHAR2 data type.

If any argument is NULL, TO_CHAR(number) returns NULL.

Oracle `TO_CHAR(number)` Examples

Here are some examples that demonstrate the usage of the Oracle TO_CHAR(number) function.

Basic Usage

Here’s a simple example using the Oracle TO_CHAR(number) function to convert 123456.789 to a character type.

SELECT TO_CHAR(123456.789)
FROM dual;

输出：

TO_CHAR(123456.789)
______________________
123456.789

Although the output and input look the same, you can use the DUMP() function to see the difference between them:

SELECT
    DUMP(123456.789),
    DUMP(TO_CHAR(123456.789))
FROM dual;

输出：

DUMP(123456.789)                   DUMP(TO_CHAR(123456.789))
__________________________________ ______________________________________________
Typ=2 Len=6: 195,13,35,57,79,91    Typ=1 Len=10: 49,50,51,52,53,54,46,55,56,57

Format

The Oracle TO_CHAR(number) function allows you to specify the output format:

SELECT TO_CHAR(1234567.89, '9G999G999D99')
FROM dual;

输出：

TO_CHAR(1234567.89,'9G999G999D99')
_____________________________________
 1,234,567.89

In this example, 9 represents any digit, G represents the localized thousands separator, and D represents the localized decimal point.

You can also use 0 to add leading or trailing zeroes:

SELECT TO_CHAR(1234567.89, '009G999G999D000')
FROM dual;

输出：

TO_CHAR(1234567.89,'009G999G999D000')
________________________________________
 001,234,567.890

Alternatively, you can use an FM prefix to accommodate numbers of different lengths:

SELECT TO_CHAR(1234567.89, 'FM999G999G999G999G999G999D99')
FROM dual;

输出：

TO_CHAR(1234567.89,'FM999G999G999G999G999G999D99')
_____________________________________________________
1,234,567.89

1,234,567.89`

Please refer to the Oracle Numeric Format Models for more information.

Conclusion