Minor formatting problem - not urgent
DBAsupport.com Forums - Powered by vBulletin
Results 1 to 4 of 4

Thread: Minor formatting problem - not urgent

  1. #1
    Join Date
    Dec 1999
    Location
    Purgatory
    Posts
    346

    Minor formatting problem - not urgent

    Hi All,

    I've got a cold and can't think straight, so while I'm working on this, I thought I'd post it here as a quick and easy puzzle (hopefully)

    I need to output a column, with a leading zero.

    Column is PURN NUMBER(10).

    I get this:-

    select PURN from PERSON
    where rownum < 3;

    PURN
    ---------
    400000001
    400000002

    col purn for 0999999999

    /

    PURN
    -----------
    0400000001
    0400000002

    See, it puts a blank before the zero for some reason. Can this be done without resorting to single row functions ?

  2. #2
    Join Date
    Dec 1999
    Location
    Purgatory
    Posts
    346
    You don't see the blank on the forum, try it in sqlplus and you'll see what I mean.

    It looks like -

    PURN
    -----------
    blank0400000001
    blank0400000002

  3. #3
    Join Date
    Dec 2000
    Location
    Ljubljana, Slovenia
    Posts
    4,439
    I hope you understand this is purely SQL*Plus isue - what you are trying to achive will work only in SQL*Plus. For any general solution, you would need to use the proper format mask with TO_CHAR function.

    Anyway, if you insist on SQL*Plus with COLUMN formating command, then use this:

    Code:
    col purn for 0999999999MI
    Your blank is reserved for a minus sign. With the above setting you are instructing SQL*Plus to display sign at the end of the string. Plus sign is not displayed by default, but if you have negative numbers the minus sign will appear at the right side.
    Jurij Modic
    ASCII a stupid question, get a stupid ANSI
    24 hours in a day .... 24 beer in a case .... coincidence?

  4. #4
    Join Date
    Dec 1999
    Location
    Purgatory
    Posts
    346
    Thanks Jurij,

    That did the trick. It was only for a simple report.

Posting Permissions

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


Click Here to Expand Forum to Full Width