Results 1 to 5 of 5
  1. #1
    Join Date
    Dec 2004
    Location
    Malaysia
    Posts
    24

    Unanswered: need help - ~oracle beginner~

    hye, how do i center the data that display in prompt...
    for example
    Employee
    -----------
    dsadsad
    dsadsad
    dsadsad

    means that the data that display is align center....
    if the data align to rite or left... i'll use lpad(x) or rpad(x)....
    but how do i want to center it in sql syntax....plz help me....
    tq

  2. #2
    Join Date
    Jan 2004
    Location
    Croatia, Europe
    Posts
    4,094
    Provided Answers: 4
    How will you know the length of the prompt?

  3. #3
    Join Date
    Dec 2004
    Location
    Malaysia
    Posts
    24
    what do you mean by
    "How will you know the length of the prompt?"
    ....
    sorry i don't know what u r trying to say....
    if you try use oracle in command prompt...
    if u want to align the data... u dun have to know the length of the prompt..
    u just type like this..
    >select rpad(ename) from emp;
    so it will display the data align to right....

  4. #4
    Join Date
    Jan 2004
    Location
    Croatia, Europe
    Posts
    4,094
    Provided Answers: 4
    OK, you have this:
    Code:
    SQL> select empno, ename from emp where deptno = 10;
    
         EMPNO ENAME
    ---------- ----------
          7782 CLARK
          7839 KING
          7934 MILLER
    and would like to have something like this:
    Code:
       EMPNO      ENAME
    ---------- ----------
       7782      CLARK
       7839       KING
       7934      MILLER
    (I drew it; this output is NOT result of a query). Both columns are 10 characters long. What if a query looks like this:
    Code:
    SQL> select empno, ename ||' '|| job emp_job from emp where deptno = 10;
    
         EMPNO EMP_JOB
    ---------- --------------------
          7782 CLARK MANAGER
          7839 KING PRESIDENT
          7934 MILLER CLERK
    Now the second column isn't 10, but 20 characters long. And what if it looks like this:
    Code:
    SQL> select empno,
      2    substr(ename ||' '|| job ||' '|| to_char(hiredate, 'dd.mm.yyyy.'), 1, 23) ejh
      3  from emp
      4  where deptno = 10;
    
         EMPNO EJH
    ---------- -----------------------
          7782 CLARK MANAGER 09.06.198
          7839 KING PRESIDENT 17.11.19
          7934 MILLER CLERK 23.01.1982
    Now the second column is 23 characters long (because of a SUBSTR function).

    So my question was: how can you guess length of a column so that you'd be able to dynamically perform some kind of a align center function to the output?

    I'd say that doing such a thing is close to impossible in SQL*Plus environment. If you want to have a properly formatted output, learnn how to use a tool like Oracle Report Builder or Crystal Reports, for example. There, in a GUI environment, you'll perform such a task with no effort at all.

    P.S. RPAD(ename) wouldn't align anything to right, but end with an error.

  5. #5
    Join Date
    Jun 2004
    Location
    Liverpool, NY USA
    Posts
    2,509
    Assuming that you know the length of the display (using column is a good way), then it is fairly easy.

    column col1 format a10

    select lpad(col1, floor((10 - length(col1))/2),' ')
    from my_table;
    Bill
    You do not need a parachute to skydive. You only need a parachute to skydive twice.

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •