Skip to Main Content

Breadcrumb

Question and Answer

Chris Saxon

Thanks for the question.

Asked: October 19, 2016 - 8:12 pm UTC

Last updated: October 21, 2016 - 9:25 am UTC

Version: 12.

Viewed 10K+ times! This question is

You Asked

Hello,
I'm a bit confused by SQL Refenrence
Lets consider Elements 0 and 9 of "Table 2-14 Number Format Elements" of the current "SQL Reference"
Lets also consider the emxamples of "Table 2-15 Results of Number Conversions"

I have SO much to ask as I think the documentation is kind of misleading

What's the meaning of mixing 0's and 9's in a same format ?
I'm asking this as Element 0 of the Table 2-14 is only described as used for leading/trailing zeros (correct me if I'm wrong but I assume leading/trailing 0's are extra 0's one might want to add to a number)


For instance,
- Does 90.99 means "return at 3 digits (i.e the three 9's) and include 0 before decimal pointif needed"
whith that format, TO_CHAR(0,'90.99') returns ' 0.00'
- Then what's the deal with 999.009 for which TO_CHAR(123.0,'999.009') returns ' 123.00' that is 5 digits (instead of 4 i.e four 9's in the format) and trailing 0's although the decimal part of the format is .009 which is not a trailing 0 format.


Also, could you explain me this sentence of Element 9
Leading zeros are blank, except for a zero value, which returns a zero for the integer part of the fixed-point number.

According to it, TO_CHAR(0,'9999') should return ' ' as 0 is not a fixed-point number

Pheww kind of confusing

and Chris said...

9 blank pads with a leading space. 0 zero pads.

So 90.99 means:

Return six characters:

- A leading space
- A digit or a space
- A digit or zero if needed
- The period
- Two digits. These are zero if none is specified.

So apply this to 0 gives ' 0.00', with two blank spaces!

This is easier to see if you dump the output:

SQL> select dump(TO_CHAR(0,'90.99')) from dual;

DUMP(TO_CHAR(0,'90.99'))
-----------------------------------------------

Typ=1 Len=6: 32,32,48,46,48,48


TO_CHAR(123.0,'999.009') returns ' 123.00'

Nope! It returns " 123.000":

SQL> select to_char(123.0,'999.009') from dual;

TO_CHAR(
--------
 123.000


The example in the docs includes the FM modifier. This stops blank padding. Because the source doesn't have a third decimal place, it's trimmed:

SQL> select to_char(123.0,'FM999.009') from dual;

TO_CHAR(
--------
123.00


If you apply this mask to 123.006 you get all six digits though:

SQL> select to_char(123.006,'FM999.009') from dual;

TO_CHAR(
--------
123.006


0 is not a fixed-point number

No. But it's the integer part of a fixed point number. So it's saying strip out all the leading zeros, except the one in the units ("ones") column. That's why TO_CHAR(0,'9999') returns zero.

The following comparing different masks on the same input may help:

SQL> with rws as (
  2    select 12.34 x from dual union all
  3    select 12 x from dual union all
  4    select .34 x from dual union all
  5    select 0 x from dual
  6  )
  7    select to_char(x, '99.99') "99.99",
  8           to_char(x, '90.99') "90.99",
  9           to_char(x, '00.00') "00.00",
 10           to_char(x, 'FM99.99') "FM99.99",
 11           to_char(x, 'FM00.00') "FM00.00"
 12    from rws;

99.99  90.99  00.00  FM99.9 FM00.0
------ ------ ------ ------ ------
 12.34  12.34  12.34 12.34  12.34
 12.00  12.00  12.00 12.    12.00
   .34   0.34  00.34 .34    00.34
   .00   0.00  00.00 0.     00.00

Rating

  (1 rating)

Is this answer out of date? If it is, please let us know via a Comment

Comments

So how 0's aren't so much different of 9's ?

A reader, October 20, 2016 - 8:21 pm UTC

0's and 9's will produce leading space for positive number and (NO leading space + minus sign) for negative numbers WHEN in front of format mask (ex : 0999 or 9999)

The thing I still don't understand is why do they find a need to mix 0's and 9's ; especially 0's after 9's before the decimal separator or 0's before 9's after the decimal separator ?

In the doc, 0's are suppose to be used for trailing/leading zeros.

For the number 123.012, all the following mask give the same result (i.e '_123.012') : '999.999' ; '000.000' ; '900.009' ; '999.000' .... hmmm why would you use the last three mask ?

Chris Saxon
October 21, 2016 - 9:25 am UTC

For the number 123.012, all the following mask give the same result

Yes. That's because you have a number with three digits before and after the decimal point. So leading/trailing zeros won't come into play. If you use a single digit integer the different you can see the difference:

select to_char(1, '999.999'),
       to_char(1, '000.000'),
       to_char(1, '900.009'),
       to_char(1, '999.000')
from   dual;

TO_CHAR(1,'999.999')  TO_CHAR(1,'000.000')  TO_CHAR(1,'900.009')  TO_CHAR(1,'999.000')  
   1.000               001.000                01.000                 1.000


And there's further differences when you use the FM mask:

select to_char(1, 'FM999.999'),
       to_char(1, 'FM000.000'),
       to_char(1, 'FM900.009'),
       to_char(1, 'FM999.000')
from   dual;

TO_CHAR(1,'FM999.999')  TO_CHAR(1,'FM000.000')  TO_CHAR(1,'FM900.009')  TO_CHAR(1,'FM999.000')  
1.                      001.000                 01.00                   1.000


But yes, the docs are unclear. Particularly with respect to the difference the FM mask makes.
more

Connor and Chris don't just spend all day on AskTOM. You can also catch regular content via Connor's blog and Chris's blog. Or if video is more your thing, check out Connor's latest video and Chris's latest video from their Youtube channels. And of course, keep up to date with AskTOM via the official twitter account.