Numeric data has cultural characteristics that international applications must address. For example, numeric separators (decimal and thousands separators) and currency symbols differ across locales and regions. Therefore, OpenEdge applications provide the capability to store, manage and display data in formats that meet the needs of the international market. The following table defines the number formats that are supported by OpenEdge SQL.

Table 1. OpenEdge SQL Number Formats
Format Example Definition
$ $999 Returns a value with a leading dollar sign.
, 9,999 Returns a comma in the specified position (not a thousands separator).
. 99.99 Returns a decimal in the specified position (not a fractional indicator).
0 0999 9990 Displays and positions a leading or trailing zero.
9 9999 Sets the number of significant digits to be displayed. Displays the leading space if positive, leading minus if negative. Leading zeros are blank except for a zero value returning a zero for the integer part of the number.
D 99D9 Returns NLS_NUMERIC_CHARACTER in the specified position. The default D character is (.).
G 9G99 Returns NLS_NUMERIC_CHARACTER in the specified position. The default G character is (,).
L L999 Return the local currency symbol NLS_CURRENCY in the specified position.

Example

The following example illustrates the use of the $ number format:
SELECT C1, TO_CHAR (C1, '$999')FROM TBL1 ORDER BY C1;
            C1  TO_CHAR(C1,$999)
--------------------------------------------------------------
-123456789.012  -$123456789
       -30.000  -$30
 123456789.012  $123456789
The following example illustrates the use of the , number format:
SELECT C1, TO_CHAR (C1, '9,999')FROM TBL1 ORDER BY C1;
            C1  TO_CHAR(C1,9,999)
--------------------------------------------------------------
-123456789.012  -123,456,789
       -30.000  -30
 123456789.012  123,456,789