Oracle - Why does the leading zero of a number disappear when converting it TO CHAR

Question

In Oracle  when converting a number with a leading zero to a character  why does the leading number disappear   Is this logic Oracle specific  or specific to SQL   Example   SELECT TO CHAR 0 56  FROM DUAL     Result    56

User · Answer

I was looking for a way to format numbers without leading or trailing spaces, periods, zeros (except one leading zero for numbers less than 1 that should be present).

This is frustrating that such most usual formatting can't be easily achieved in Oracle.

Even Tom Kyte only suggested long complicated workaround like this:

case when trunc(x)=x
    then to_char(x, 'FM999999999999999999')
    else to_char(x, 'FM999999999999999.99')
end x

But I was able to find shorter solution that mentions the value only once:

rtrim(to_char(x, 'FM999999999999990.99'), '.')

This works as expected for all possible values:

select 
    to_char(num, 'FM99.99') wrong_leading_period,
    to_char(num, 'FM90.99') wrong_trailing_period,
    rtrim(to_char(num, 'FM90.99'), '.') correct
from (
  select num from (select 0.25 c1, 0.1 c2, 1.2 c3, 13 c4, -70 c5 from dual)
  unpivot (num for dummy in (c1, c2, c3, c4, c5))
) sampledata;

    | WRONG_LEADING_PERIOD | WRONG_TRAILING_PERIOD | CORRECT |
    |----------------------|-----------------------|---------|
    |                  .25 |                  0.25 |    0.25 |
    |                   .1 |                   0.1 |     0.1 |
    |                  1.2 |                   1.2 |     1.2 |
    |                  13. |                   13. |      13 |
    |                 -70. |                  -70. |     -70 |

Still looking for even shorter solution.

There is a shortening approarch with custom helper function:

create or replace function str(num in number) return varchar2
as
begin
    return rtrim(to_char(num, 'FM999999999999990.99'), '.');
end;

But custom pl/sql functions have significant performace overhead that is not suitable for heavy queries.

User · Answer

Seems like the only way to get decimal in a pretty  for me  form requires some ridiculous code   The only solution I got so far   CASE WHEN xy gt 0 and xy lt 1 then  0     to char xy  else to char xy    xy is a decimal   xy             query result 0 8            0 8  --not sth like  80 10             10  --not sth like 10 00

User · Answer

That only works for numbers less than 1   select to char 12 34   0D99   from dual  -- Result          This won t work   You could do something like this but this results in leading whitespaces   select to char 12 34   999990D99   from dual  -- Result        12 34    Ultimately  you could add a TRIM to get rid of the whitespaces again but I wouldn t consider that a proper solution either     select trim to char 12 34   999990D99    from dual  -- Result  12 34   Again  this will only work for numbers with 6 digits max   Edit  I wanted to add this as a comment on DCookie s suggestion but I can t

User · Answer

Below format try if number is like   ex 1 suppose number like 10 1 if apply below format it will be come as 10 10  ex 2 suppose number like   02 if apply below format it will be come as 0 02  ex 3 suppose number like  0 2 if apply below format it will be come as 0 20  to char round to number column name  10000000 2   999999999990D99   as column name

User · Answer

Should work in all cases   SELECT regexp replace 0 1234     -             10 2   FROM dual

User · Answer

Try this to avoid to char limitations   SELECT  regexp replace regexp replace n   -    s  -0   s        s  0   s  FROM  SELECT -0 89 n RTrim 1 2 5  s FROM dual

User · Answer

It s the default formatting that Oracle provides  If you want leading zeros on output  you ll need to explicitly provide the format  Use   SELECT TO CHAR 0 56  0 99   FROM DUAL    or even   SELECT TO CHAR  56  0 99   FROM DUAL    The same is true for trailing zeros   SQL gt  SELECT TO CHAR  56  0 990   val FROM DUAL   VAL ------  0 560   The general form of the TO CHAR conversion function is   TO CHAR number  format

[sql] Oracle - Why does the leading zero of a number disappear when converting it TO_CHAR

Examples related to sql

Examples related to oracle

Examples related to oracle10g