CODEWITHSUNDEEP
sqlsqlplus
user2420374
user2420374

Reputation: 147

SQL*Plus column separator

I'm in scott user in SQL*Plus and is it possible in this select to separate columns by tabulation instead of the default space?

SELECT empno, ename, job FROM emp

Unfortunately this doesn't work:

SELECT empno '\t', ename '\t', job '\t' FROM emp

Instead of:

 EMPNO ENAME      JOB

  7369 SMITH      CLERK
  7499 ALLEN      SALESMAN
  7521 WARD       SALESMAN
  7566 JONES      MANAGER
  7654 MARTIN     SALESMAN
  7698 BLAKE      MANAGER
  7782 CLARK      MANAGER
  7839 KING       PRESIDENT
  7844 TURNER     SALESMAN
  7900 JAMES      CLERK
  7902 FORD       ANALYST
  7934 MILLER     CLERK

I want more space between columns:

      EMPNO     ENAME           JOB
 ----------     ----------      ---------

       7369     SMITH           CLERK
       7499     ALLEN           SALESMAN
       7521     WARD            SALESMAN
       7566     JONES           MANAGER
       7654     MARTIN          SALESMAN
       7698     BLAKE           MANAGER
       7782     CLARK           MANAGER
       7839     KING            PRESIDENT
       7844     TURNER          SALESMAN
       7900     JAMES           CLERK
       7902     FORD            ANALYST
       7934     MILLER          CLERK

Upvotes: 1

Views: 2733

Answers (3)

access_granted
access_granted

Reputation: 1907

An easier solution would be:

set colsep ' '

Upvotes: 1

Patrick Hofman
Patrick Hofman

Reputation: 156978

As sqlab already pointed out you need the COLUMN formatting options which are in SQL*PLUS.

Since the docs aren't very readable, here is what you need:

COLUMN EMPNO FORMAT A10
COLUMN ENAME FORMAT A16
COLUMN JOB FORMAT A9

If you need to do it in regular SQL you can use this:

SELECT rpad(empno, ' ', 11) empno
,      rpad(ename, ' ', 17) ename
,      job
FROM   emp

Upvotes: 0

sqlab
sqlab

Reputation: 6436

Use the COLUMN command (see SQL*Plus® User's Guide and Reference) and format your report according your wish

Upvotes: 1

Related Questions