Column format
The COLUMN command has the syntax:
COLUMN column-name FORMAT format-specificationThe format-specificationuses a's for text and 9's for numbers:
| Format | Description |
|---|---|
| an | text format for a field width of n |
| 9n | numeric format with no decimals for a field width of numbers of size n |
CREATE TABLE EMP (EMPNO NUMBER(4) NOT NULL,
ENAME VARCHAR2(10),
JOB VARCHAR2(9),
SAL NUMBER(7, 2),
DEPTNO NUMBER(2));
INSERT INTO EMP VALUES (1, 'SMITH', 'CLERK', 800, 20);
INSERT INTO EMP VALUES (2, 'ALLEN', 'SALESMAN', 1600, 30);
INSERT INTO EMP VALUES (3, 'WARD', 'SALESMAN', 1250.12, 30);
INSERT INTO EMP VALUES (4, 'JONES', 'MANAGER', 2975.23, 20);
INSERT INTO EMP VALUES (5, 'MARTIN','SALESMAN', 1250.23, 30);
INSERT INTO EMP VALUES (6, 'BLAKE', 'MANAGER', 2850, 30);
INSERT INTO EMP VALUES (7, 'CLARK', 'MANAGER', NULL, 10);
INSERT INTO EMP VALUES (8, 'SCOTT', 'ANALYST', 3000, 20);
INSERT INTO EMP VALUES (9, 'KING', 'PRESIDENT',NULL, 10);
INSERT INTO EMP VALUES (10,'TURNER','SALESMAN', 1500, 30);
INSERT INTO EMP VALUES (11,'ADAMS', 'CLERK', 0.99, 20);
SQL> COLUMN ENAME FORMAT a10;
SQL>
SQL> select * from emp;
EMPNO ENAME JOB SAL DEPTNO
---------- ---------- --------- ---------- ----------
1 SMITH CLERK 800 20
2 ALLEN SALESMAN 1600 30
3 WARD SALESMAN 1250.12 30
4 JONES MANAGER 2975.23 20
5 MARTIN SALESMAN 1250.23 30
6 BLAKE MANAGER 2850 30
7 CLARK MANAGER 10
8 SCOTT ANALYST 3000 20
9 KING PRESIDENT 10
10 TURNER SALESMAN 1500 30
11 ADAMS CLERK .99 20
11 rows selected.
SQL>
We can shorten the ename field with
SQL> COLUMN ENAME FORMAT a6;
SQL> select * from emp;
EMPNO ENAME JOB SAL DEPTNO
---------- ------ --------- ---------- ----------
1 SMITH CLERK 800 20
2 ALLEN SALESMAN 1600 30
3 WARD SALESMAN 1250.12 30
4 JONES MANAGER 2975.23 20
5 MARTIN SALESMAN 1250.23 30
6 BLAKE MANAGER 2850 30
7 CLARK MANAGER 10
8 SCOTT ANALYST 3000 20
9 KING PRESIDENT 10
10 TURNER SALESMAN 1500 30
11 ADAMS CLERK .99 20
11 rows selected.
SQL>
For alphanumeric columns, if the column is too short to fit the data, it will be displayed on multiple lines.
SQL> COLUMN ENAME FORMAT a3;
SQL> select * from emp;
EMPNO ENA JOB SAL DEPTNO
---------- --- --------- ---------- ----------
1 SMI CLERK 800 20
TH
2 ALL SALESMAN 1600 30
EN
3 WAR SALESMAN 1250.12 30
D
4 JON MANAGER 2975.23 20
ES
EMPNO ENA JOB SAL DEPTNO
---------- --- --------- ---------- ----------
5 MAR SALESMAN 1250.23 30
TIN
6 BLA MANAGER 2850 30
KE
7 CLA MANAGER 10
RK
8 SCO ANALYST 3000 20
EMPNO ENA JOB SAL DEPTNO
---------- --- --------- ---------- ----------
TT
9 KIN PRESIDENT 10
G
10 TUR SALESMAN 1500 30
NER
11 ADA CLERK .99 20
MS
11 rows selected.
SQL>
For simple formatting of numbers:
SQL> COLUMN empno FORMAT 99999
SQL> SELECT empno FROM emp;
EMPNO
------
1
2
3
4
5
6
7
8
9
10
11
11 rows selected.
SQL>
SQL>
With numbers, if the format size is less than the heading size, then the field width defaults to be the heading size.
SQL> COLUMN empno FORMAT 9
SQL> SELECT empno FROM emp;
EMPNO
-----
1
2
3
4
5
6
7
8
9
##
##
11 rows selected.
SQL>
If there are decimals or if commas are desired:
SQL>
SQL> COLUMN sal FORMAT 99999.99
SQL>
SQL> SELECT * FROM emp;
EMPNO ENA JOB SAL DEPTNO
----- --- --------- --------- ----------
1 SMI CLERK 800.00 20
TH
2 ALL SALESMAN 1600.00 30
EN
3 WAR SALESMAN 1250.12 30
D
4 JON MANAGER 2975.23 20
ES
EMPNO ENA JOB SAL DEPTNO
----- --- --------- --------- ----------
5 MAR SALESMAN 1250.23 30
TIN
6 BLA MANAGER 2850.00 30
KE
7 CLA MANAGER 10
RK
8 SCO ANALYST 3000.00 20
EMPNO ENA JOB SAL DEPTNO
----- --- --------- --------- ----------
TT
9 KIN PRESIDENT 10
G
## TUR SALESMAN 1500.00 30
NER
## ADA CLERK .99 20
MS
11 rows selected.
SQL>
To add format the the number:
SQL> COLUMN SAL FORMAT 999,999
SQL> SELECT * FROM emp;
EMPNO ENA JOB SAL DEPTNO
----- --- --------- -------- ----------
1 SMI CLERK 800 20
TH
2 ALL SALESMAN 1,600 30
EN
3 WAR SALESMAN 1,250 30
D
4 JON MANAGER 2,975 20
ES
EMPNO ENA JOB SAL DEPTNO
----- --- --------- -------- ----------
5 MAR SALESMAN 1,250 30
TIN
6 BLA MANAGER 2,850 30
KE
7 CLA MANAGER 10
RK
8 SCO ANALYST 3,000 20
EMPNO ENA JOB SAL DEPTNO
----- --- --------- -------- ----------
TT
9 KIN PRESIDENT 10
G
## TUR SALESMAN 1,500 30
NER
## ADA CLERK 1 20
MS
11 rows selected.
SQL>
Numbers can be output with leading zeros or dollar signs.
SQL>
SQL> COLUMN SAL FORMAT $999,999
SQL> SELECT * FROM emp;
EMPNO ENA JOB SAL DEPTNO
----- --- --------- --------- ----------
1 SMI CLERK $800 20
TH
2 ALL SALESMAN $1,600 30
EN
3 WAR SALESMAN $1,250 30
D
4 JON MANAGER $2,975 20
ES
EMPNO ENA JOB SAL DEPTNO
----- --- --------- --------- ----------
5 MAR SALESMAN $1,250 30
TIN
6 BLA MANAGER $2,850 30
KE
7 CLA MANAGER 10
RK
8 SCO ANALYST $3,000 20
EMPNO ENA JOB SAL DEPTNO
----- --- --------- --------- ----------
TT
9 KIN PRESIDENT 10
G
## TUR SALESMAN $1,500 30
NER
## ADA CLERK $1 20
MS
11 rows selected.
SQL>
SQL> COLUMN ENAME FORMAT a10;
SQL> COLUMN SAL FORMAT 000999999
SQL> SELECT * FROM emp;
EMPNO ENAME JOB SAL DEPTNO
----- ---------- --------- ---------- ----------
1 SMITH CLERK 000000800 20
2 ALLEN SALESMAN 000001600 30
3 WARD SALESMAN 000001250 30
4 JONES MANAGER 000002975 20
5 MARTIN SALESMAN 000001250 30
6 BLAKE MANAGER 000002850 30
7 CLARK MANAGER 10
8 SCOTT ANALYST 000003000 20
9 KING PRESIDENT 10
## TURNER SALESMAN 000001500 30
## ADAMS CLERK 000000001 20
11 rows selected.
SQL>
To avoid only decimal points, insert a zero in front of the decimal if the amount were less than one.
SQL> COLUMN sal FORMAT 990.99
SQL> SELECT * FROM emp;
EMPNO ENAME JOB SAL DEPTNO
----- ---------- --------- ------- ----------
1 SMITH CLERK 800.00 20
2 ALLEN SALESMAN ####### 30
3 WARD SALESMAN ####### 30
4 JONES MANAGER ####### 20
5 MARTIN SALESMAN ####### 30
6 BLAKE MANAGER ####### 30
7 CLARK MANAGER 10
8 SCOTT ANALYST ####### 20
9 KING PRESIDENT 10
## TURNER SALESMAN ####### 30
## ADAMS CLERK 0.99 20
11 rows selected.
SQL>