Oracle TO_NCHAR(number) Function

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

Oracle `TO_NCHAR(number)` Syntax

Here is the syntax of the Oracle TO_NCHAR(number) function:

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

Parameters

n

Required. The numeric value to convert.

fmt

Optional. The format string, such as: L99G999D99MI.

nlsparam

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

Decimal character
Group separator
Local currency symbol
International currency symbol

This parameter can be in 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. Within the quoted string, two single quotes must be used around the parameter value. There are ten characters available for the currency symbol.

If 'nlsparam' or any parameter is omitted, the function uses the default parameter values of your session.

Return Value

Oracle TO_NCHAR(number) function returns.

If any parameter is NULL, TO_NCHAR(number) returns NULL.

Oracle `TO_NCHAR(number)` Examples

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

Basic Usage

Here is a simple example that uses the Oracle TO_NCHAR(number) function to convert 123456.789 to a character type.

SELECT TO_NCHAR(123456.789)
FROM dual;

输出：

TO_NCHAR(123456.789)
_______________________
123456.789

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

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

输出：

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

Format

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

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

输出：

TO_NCHAR(1234567.89,'9G999G999D99')
______________________________________
 1,234,567.89

In this example, 9 represents any digit, G represents localized group separators, and D represents localized decimal point.

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

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

输出：

TO_NCHAR(1234567.89,'009G999G999D000')
_________________________________________
 001,234,567.890

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

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

输出：

TO_NCHAR(1234567.89,'FM999G999G999G999G999G999D99')
______________________________________________________
1,234,567.89

Refer to the Oracle Numeric Format Models for more information.

Conclusion

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

Oracle TO_NCHAR(number) Syntax