For simple formatting of numbers, we can use 9n just as we used an, where n is the width of the output field.
For example, if we format the empno field to make it shorter, we can use:
COLUMN empno FORMAT 999 SELECT empno, ename FROM employee-- from www . ja v a 2 s. c om
With numbers, if the format size is less than the heading size, then the field width defaults to be the heading size.
This is the case with empno, which is 5. If the column format is too small:
COLUMN empno FORMAT 99 SELECT empno, ename FROM employee ;-- www.j a v a2s . c o m If there are decimals or if commas are desired, the following formats are available:
COLUMN orig_salary FORMAT 999,999 COLUMN curr_salary FORMAT 99999.99 -- from ww w .j a v a 2 s . co m SELECT empno, ename, orig_salary, curr_salary FROM employee;
Numbers can also be output with leading zeros or dollar signs if desired.
To avoid having "naked" decimal points you could insert a zero in front of the decimal if the amount were less than one.
If a zero is placed in the numeric format, it says, "put a zero here if it would be null."
COLUMN orig_salary FORMAT 999990.99 COLUMN orig_salary FORMAT 999909.99 -- from w w w . j a v a 2 s . com SELECT empno, ename, orig_salary, curr_salary FROM employee;
The COLUMN-FORMAT statement "COLUMN amount FORMAT 900.99" produces the same result, as the second zero is superfluous.
We can also add dollar signs to the output. The dollar sign floats up to the first character displayed:
COLUMN orig_salary FORMAT $999990.99 SELECT empno, ename, orig_salary, -- from w w w .java2s .c om curr_salary FROM employee;