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

Home -> Community -> Usenet -> c.d.o.misc -> Re: Leading zeros for numbers between 0 and 1

Re: Leading zeros for numbers between 0 and 1

From: Frank Suijkerbuijk <fsuijkerbuijk_at_home.nl>
Date: 18 Nov 2001 03:02:05 -0800
Message-ID: <a4916335.0111180302.5cc81b7b@posting.google.com>


Hi Michael,

one way is to use the set numformat option. If you use i.e set numformat 9,999,999,990.00000000, select .1 from dual will get you

SQL> set numformat 9,999,999,990.00000000 SQL> select .1 from dual;

                     .1
-----------------------
             0.10000000

Set back is that to much decimals will not work. SQL> select .000000000000001 from dual;

       .000000000000001


             0.00000000

or this will not work
SQL> select 11000000000 from dual;

            11000000000


#######################

So you would need to set a very good standard.

ALternatively, you could use the column command to format per column in much the same way.

Hope this helps.
Frank

michael.mauch_at_gmx.de (Michael Mauch) wrote in message news:<sh66t9.jhi.ln_at_elmicha.333200002251-0001.dialin.t-online.de>...
> Jim Kennedy <kennedy-family_at_home.com> wrote:
> > Look at the to_char function. I believe that one of the formats allows a
> > leading zero.
> > e.g. to_char(field,'0.99') which will give a leading zero and 2 decimal
> > places.
>
> Thank you for your suggestion, but I'm afraid to_char() isn't much
> easier: either the user of my export program supplies the appropriate
> to_char() around all of the numeric columns, or my program has to apply
> to_char() to all numeric columns.
>
> If there was a way to set a predefined to_char() "format model" which
> is automatically applied to all numeric data, this would be much better.
>
> For date fields there is NLS_DATE_FORMAT, but there doesn't seem to
> exist such a default setting for number fields.
>
> And then, I'm afraid I can't find a format model that works just like
> the default model 'TM9', but places a zero before the decimal point if
> the value is between 0 and 1 or between -1 and 0.
>
> It looks like all other models have a fixed number of digits, so I get
> superfluous zeros, rounding or even ###'ing.
>
> Do you know a format model that prints numbers in the following manner?
>
> 0
> 0.1
> -0.1
> 10000000000000000
> 0.000000000000000001
>
> Regards...
> Michael
Received on Sun Nov 18 2001 - 05:02:05 CST

Original text of this message

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