Oracle - Why Does the Leading Zero of a Number Disappear When Converting It To_Char

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

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> 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)

Oracle number to char leading 0

Try to use:

select TO_CHAR(x * 0,5, '990,99') from dual

EDIT:

Try to create a function like this:

create function getDecimalFormat(x in number) 
    return varchar2
as
begin
    return RTRIM(TO_CHAR(x, 'FM999999999999990.99'), '.');
end;

Values are not displaying with leading zero in oracle

You can get close with a mask like 'FM999999990D9999', with an appropriate number of 9s each side of the decimal to cover all values you might have.

with tab1 (cola) as (
         select 0.87 from dual
  union  select 661 from dual
  union  select 661.87 res from dual
  union  select 1.5 res from dual
)
select cola, to_char(cola, 'FM999999990D9999')
from tab1;

      COLA TO_CHAR(COLA,'F
---------- ---------------
       .87 0.87           
       1.5 1.5            
       661 661.           
    661.87 661.87         

The FM removes trailing zeros and leading spaces (including a nominal space for a +/- sign).

To get rid of the trailing decimal marker too you need to trim it off:

with tab1 (cola) as (
         select 0.87 from dual
  union  select 661 from dual
  union  select 661.87 res from dual
  union  select 1.5 res from dual
)
select cola, rtrim(to_char(cola, 'FM999999990D9999'), to_char(0, 'FMD'))
from tab1;

I've stuck with D in both parts of that; you could use a fixed . in both so you don't need the second to_char() call to convert that, but you may want it to be controlled by the session - either way it needs to be consistent.

---

If you don't know how many 9s you need to include, you could generate a bespoke format mask for every number, based on how many digits there are before and after the decimal separator:

with tab1 (cola) as (
            select 0.87 from dual
  union all select 661 from dual
  union all select 661.87 res from dual
  union all select 1.5 res from dual
  union all select 0.00045354543 from dual
)
select cola,
  'FM' || lpad('0', length(trunc(cola)), '9')
       || case when trunc(cola) != cola
               then 'D' || rpad('9', length(cola - trunc(cola)) - 1, '9')
          end as format_mask,
  to_char(cola,
    'FM' || lpad('0', length(trunc(cola)), '9')
         || case when trunc(cola) != cola
                 then 'D' || rpad('9', length(cola - trunc(cola)) - 1, '9')
            end) as result
from tab1;

           COLA FORMAT_MASK          RESULT              
--------------- -------------------- --------------------
            .87 FM0D99               0.87                
            661 FM990                661                 
         661.87 FM990D99             661.87              
            1.5 FM0D9                1.5                 
   .00045354543 FM0D99999999999      0.00045354543       

This relies on implicit conversion but seems to work for positive, negative and zero. It doesn't need to trim the result because the decimal separator D is only included at all for non-integers.

TO_Char number format model in Oracle

Keep in mind that you are transforming a number into a string. The number does not have any sense of "," or "." or anything--it is a number.

The trick is to get the TO_CHAR function to convert the internal number to the string representation that you want.
There are a few problems to worry about: getting the radix point (decimal) correct and dealing with padding.

Here is a working example:

SELECT to_char(0.00235,'FM99999999999999990D99999', 'NLS_NUMERIC_CHARACTERS = '',.''') FROM DUAL;

0,00235

SELECT to_char(156.45823,'FM99999999999999990D99999', 'NLS_NUMERIC_CHARACTERS = '',.''') FROM DUAL;

156,45823

SELECT to_char(-0.0235,'FM99999999999999990D99999', 'NLS_NUMERIC_CHARACTERS = '',.''') FROM DUAL;

-0,0235

SELECT to_char(-156.45623,'FM99999999999999990D99999', 'NLS_NUMERIC_CHARACTERS = '',.''') FROM DUAL;

-156,45623

SELECT to_char(123456789.45623,'FM99999999999999990D99999', 'NLS_NUMERIC_CHARACTERS = '',.''') FROM DUAL;

123456789,45623

The relevant parts of the mask:

FMis used to trim leading and trailing blanks that Oracle normally uses to pad out numbers.

D is the radix point, depending on your NLS settings.

NLS_NUMERIC_CHARACTERS ... is an override of your local NLS settings--this might not be necessary if your locale uses a comma for the decimal, but it is a way you can force this behavior in a database with, say, North American settings.

unwanted leading blank space on oracle number format

Use FM (Fill Mode), e.g.

select to_char(1011,'FM00000000') OPE_NO from dual;

---

Related Topics