When using the TO_CHAR() function to format a number in Oracle Database, you can use the B format element to return blanks for the integer part of a fixed-point number when the integer part is zero.

This works regardless of zeros in the format model.

Examples

Here’s an example to demonstrate:

SELECT  
    TO_CHAR(0, '9'),
    TO_CHAR(0, 'B9')
FROM DUAL;

Result:

   TO_CHAR(0,'9')    TO_CHAR(0,'B9') 
_________________ __________________ 
 0                                  

Here’s one with a positive integer:

SELECT  
    TO_CHAR(1, '9'),
    TO_CHAR(1, 'B9')
FROM DUAL;

Result:

   TO_CHAR(1,'9')    TO_CHAR(1,'B9') 
_________________ __________________ 
 1                 1                

And here’s one with a zero and a format model that specifies a fractional part:

SELECT  
    TO_CHAR(0, '90D99'),
    TO_CHAR(0, 'B90D99')
FROM DUAL;

Result:

   TO_CHAR(0,'90D99')    TO_CHAR(0,'B90D99') 
_____________________ ______________________ 
  0.00                                      

And with a positive integer:

SELECT  
    TO_CHAR(1, '90D99'),
    TO_CHAR(1, 'B90D99')
FROM DUAL;

Result:

   TO_CHAR(1,'90D99')    TO_CHAR(1,'B90D99') 
_____________________ ______________________ 
  1.00                  1.00