Oracle SQL TO_CHAR (num, format) - Trailing zeros

Question

In the Oracle documentation we read about the formating parameter that:

0999    Returns leading zeros.
9990    Returns trailing zeros.

I understand leading zeros: Display 000123 instead of 123.

But trailing zeros?

How is 9990 different from 9999?

How is 99.90 different from 99.99?

Answer 1

You could use trailing zeros when you are dealing with monetary values eg

Table and data

create table payments (
  payment number
) ;

insert into payments( payment )
select 11 from dual union all
select 11.1 from dual union all
select 11.11 from dual ;

Query

select payment amount from payments 
union
select sum( payment ) from payments ;

-- result

   AMOUNT 
_________ 
       11 
     11.1 
    11.11 
    33.21

If you write a query or view that applies trailing zeros, you'll be able to get a result that is more "usable" eg

create or replace view payments_view ( position_, description, amount )
as
select 
  1 
, 'item'
, to_char( payment, '999,999.00' )
from payments
union
select 
  2
, 'total'
, to_char( sum( payment ), '999,999.00' ) 
from payments
;

select description, amount
from payments_view
order by position_
;

-- output
   DESCRIPTION         AMOUNT 
______________ ______________ 
item                 11.00    
item                 11.10    
item                 11.11    
total                33.21 

DBfiddle here.

{1} How is 9990 different from 9999? {2} How is 99.90 different from 99.99?

In order to make this "visible", please have a look at the resultset of the following query, which shows various function calls, the DUMP() (returning a code for the datatype, the length of its input in bytes, and the "internal representation" - UTF8 code points in decimal in this case), and the actual output. The resultset also contains 3 rows with values padded via LPAD() and RPAD(), respectively, in order to make the positions of the digits easier to see.

select 
  'to_char( 1,''9990'' )'       function_
, dump( to_char( 1,'9990' ) )   dump_
, to_char( 1, '9990' )          output_
from dual union all
select 'to_char( 1,''9999'' )', dump( to_char( 1, '9999' ) )
, to_char( 1, '9999' )
from dual union all 
select 'to_char( 1 )', dump( to_char( 1 ) )
, to_char( 1 )
from dual union all 
select 'to_char( 1,''fm9990'' )', dump( to_char( 1,'fm9990' ) )
, to_char( 1, '9999' )
from dual union all 
select 'to_char( 1,''fm9999'' )', dump( to_char( 1,'fm9999' ) )
, to_char( 1, '9999' )
from dual union all 
select 'to_char( 0,''99.90'' )', dump( to_char( 0,'99.90' ) )
, to_char( 0, '99.90' )  
from dual union all
select 'to_char( 0,''99.99'' )', dump( to_char( 0,'99.99' ) )
, to_char( 0, '99.99' )  
from dual union all
select 'to_char( 0,''90.99'' )', dump( to_char( 0,'90.99' ) )
, to_char( 0, '90.99' )  
from dual union all
select 'to_char( 0,''fm99.90'' )', dump( to_char( 0,'fm99.90' ) )
, to_char( 0, 'fm99.90' )  
from dual union all
select 'to_char( 0,''fm99.99'' )', dump( to_char( 0,'fm99.99' ) )
, to_char( 0, 'fm99.99' )  
from dual union all
select 'lpad( ''1'', 5, '' '' )', null, lpad( '1', 5, ' ' ) from dual union all
select 'lpad( ''12345'', 6, ''_'' )', null, lpad( '12345', 6, '_' ) from dual union all
select 'rpad( ''12345'', 6, ''_'' )', null, rpad( '12345', 6, '_' ) from dual
;

Result

                 FUNCTION_                             DUMP_    OUTPUT_ 
__________________________ _________________________________ __________ 
to_char( 1,'9990' )        Typ=1 Len=5: 32,32,32,32,49           1      
to_char( 1,'9999' )        Typ=1 Len=5: 32,32,32,32,49           1      
to_char( 1 )               Typ=1 Len=1: 49                   1          
to_char( 1,'fm9990' )      Typ=1 Len=1: 49                       1      
to_char( 1,'fm9999' )      Typ=1 Len=1: 49                       1      
to_char( 0,'99.90' )       Typ=1 Len=6: 32,32,32,46,48,48       .00     
to_char( 0,'99.99' )       Typ=1 Len=6: 32,32,32,46,48,48       .00     
to_char( 0,'90.99' )       Typ=1 Len=6: 32,32,48,46,48,48      0.00     
to_char( 0,'fm99.90' )     Typ=1 Len=3: 46,48,48             .00        
to_char( 0,'fm99.99' )     Typ=1 Len=2: 48,46                0.         
lpad( '1', 5, ' ' )                                              1      
lpad( '12345', 6, '_' )                                      _12345     
rpad( '12345', 6, '_' )                                      12345_ 

Answers to your questions:

{1} No difference. In both cases, the results get padded with blanks (1 extra space for the "sign"). The effect of the formatting is comparable to the output of LPAD() (see third line from the bottom of the result set).

{2} No difference.

As @Ponder Stibbons has mentioned, there will be a difference when the number contains a decimal point and you use the 'fm' format model modifier, as this will remove extraneous characters from the output.

DBfiddle here.