SQL* Plus CLI  «Prev  Next»

Lesson 5Formatting numeric columns
ObjectiveControl the display of numeric columns.

Formatting Numeric Columns(control display)

Numeric columns require a different set of formatting characters than those used for text columns. With numeric columns, you not only need to worry about column width, but also about where to put the commas, where to place the decimal point, how to display negative numbers, and other formatting issues.

Numeric formatting characters

The following table shows the different characters that may be used in a numeric format string, and provides examples of their effect on the printed output.

CharacterDescriptionIf you use this format specificationTo display this valueThe output will look like this.
9"9"s are used to represent digits in the output999
999999
123
123
123
123
0Same as 9, but does not suppress leading zeros099
099999
990999
123
123
123
123
000123
0123
. Marks the decimal point999.99 123.45 123.45
, Marks the location of commas in the output999,999.99 123456.78 123,456.78
$ Places a leading dollar sign in the output$999,999.99 123456.78 $123,456.78
PR Causes negative numbers to display in parentheses(999.99) -123 (123.45)
S Forces a sign to be displayed, even for positive numbers. May be placed at either end of the number.S999
999s
-123
123
-123
123+

The following example shows some sample output using three different formats. The SELECT statement selects three values from a table named dual. The values are each formatted differently, so that you can see the effect of the different formats.

SQL> COLUMN a FORMAT 999,999.99
SQL> COLUMN b FORMAT 099,999.99
SQL> COLUMN c FORMAT $999,999.99
SQL> SELECT 123.45 a, 
  2         234.56 b,
  3         345.67 c
  4  FROM dual;
A           B            C
----------- ----------- ------------
     123.45  000,123.45      $123.45
No matter how you format numbers, SQL*Plus always leaves space for a possible negative value. Thus a numeric column that is formatted as 999 will display four spaces wide to accommodate the potential negative sign.