Reputation: 2756
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
?
Upvotes: 2
Views: 2558
Reputation: 2252
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.
Upvotes: 1