Oracle FAQ | Your Portal to the Oracle Knowledge Grid |
Home -> Community -> Usenet -> c.d.o.misc -> Re: Leading zeros for numbers between 0 and 1
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