Oracle SQL: the Essential Reference

TO_CHAR (Converting Numbers to Character Strings)

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

Converts a numeric value into a character-based representation of that value.

Parameters

n

Specifies a numeric value to convert.

fmt

Specifies a number format specification made up of the elements shown in Table 5-5.

nlsparams

Specifies a character string made up of one or more of the following elements, allowing you to specify various NLS characteristics for the result.

NLS_NUMERIC_CHARACTERS='' dg''

Allows you to specify the characters to use for decimal points and group separators. The d character becomes the decimal point and the g character becomes the group separator. For this parameter to take effect, you must use D and G in your format specification to mark the location of the decimal point and group separator, respectively.

NLS_CURRENCY='' text''

Allows you to specify the currency symbol (up to 10 characters long) to use in place of any L characters contained in the format specification.

NLS_ISO_CURRENCY='' territory''

Allows you to specify the NLS territory whose currency symbol you want to use in place of any C characters contained in the format specification.

The nlsparams settings are strings embedded within a string. The quotes you see around the values are doubled single quotes, not double quotes.

Example

SQL> SELECT TO_CHAR(75917.63,'$99,999.99') a,
  2   TO_CHAR(75917.63,'L99G999D99', ...