Oracle SQL - COLUMN - Formatting Numbers

  1. HOME
  2. Oracle SQL
  3. SQL Plus
  4. COLUMN

Introduction

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:

Demo

COLUMN empno FORMAT 999 

SELECT empno, ename 
FROM employee-- from  w  w  w .  j a v  a 2  s .co m

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:

Demo

COLUMN empno FORMAT 99 

SELECT empno, ename 
FROM employee ;-- from   ww  w. java 2 s  . com

If there are decimals or if commas are desired, the following formats are available:

Demo

COLUMN orig_salary FORMAT 999,999 
COLUMN curr_salary FORMAT 99999.99 -- from   w w w .j a  v a2s . c  o 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."

For example:

Demo

COLUMN orig_salary FORMAT 999990.99 
COLUMN orig_salary FORMAT 999909.99 --   w w w.  ja v a  2s  .  co m
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:

Demo

COLUMN orig_salary FORMAT $999990.99 

SELECT empno, ename, 
  orig_salary, -- from w  w w  . j av a 2s  .  c  om
  curr_salary 
FROM employee;

Related Topic