Oracle FAQ Your Portal to the Oracle Knowledge Grid
HOME | ASK QUESTION | ADD INFO | SEARCH | E-MAIL US
 

Home -> Community -> Usenet -> c.d.o.tools -> Re: How to suppress the leading space of a number column?

Re: How to suppress the leading space of a number column?

From: Sybrand Bakker <postbus_at_sybrandb.demon.nl>
Date: Tue, 17 Apr 2001 18:14:21 +0200
Message-ID: <uqqodtcam2ivdpvc6cqmbd8cth1g5jl6lu@4ax.com>

On Tue, 17 Apr 2001 17:52:55 +0800, Dino Hsu <dino1_nospam_at_ms1.hinet.net> wrote:

>Dear all,
>
>I am very much frustrated by the number formatting of SQL Plus,
>because it always add one more space in front of a number column,
>which is not necessary for me. For example:
>
><SQL script begin>
>set space 0
>column n1 format 999
>column n3 format 999
>column n5 format 999
>column c2 format a3
>column c4 format a3
>column c6 format a3
>
>select 100 n1, '200' c2, 300 n3, '400' c4, 500 n5, '600' c6 from dual;
><SQL script end>
>
><SQL result begin>
> N1C2 N3C4 N5C6
>---------------------
> 100200 300400 500600
>^ ^ ^
>
>1 row selected.1 row selected.
><SQL result end>
>
>The additional space will cause errors in my interface file (in this
>case, these column are always positive or zero). On the other hand, if
>I change the number formats to 99 instead of 999, the values become
>'###', which means undisplayable. Any comments? Thanks.
>
>Dino

As in my previous reply: use to_char(number expression,'fm<mask>') where fm is shorthand for fill mode.
The space is the sign bit, it will be - for negative numbers. Of course this is documented behavior, and I really must urge you to read them.

Regards,

Sybrand Bakker, Oracle DBA Received on Tue Apr 17 2001 - 11:14:21 CDT

Original text of this message

HOME | ASK QUESTION | ADD INFO | SEARCH | E-MAIL US