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

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

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

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

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

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

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

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

[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