Formatting Numeric Columns(control display)

Lesson 5	Formatting numeric columns
Objective	Control 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.

Character	Description	If you use this format specification	To display this value	The output will look like this.
9	"9"s are used to represent digits in the output	999 999999	123 123	123 123
0	Same as 9, but does not suppress leading zeros	099 099999 990999	123 123 123	123 000123 0123
.	Marks the decimal point	999.99	123.45	123.45
,	Marks the location of commas in the output	999,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.