CODEWITHSUNDEEP
oracle-databasecastingfloating-pointtype-conversion
Bob
Bob

Reputation: 1001

Oracle Format Float Numbers 11g

I want to format

if integer, then integer.00

if float, then float.xx (2 digits of precision)

I have a division operation whose value may result in an integer or float. I want to store the result with 2 digits of precision. How can I achieve this?

Upvotes: 1

Views: 12558

Answers (2)

hol
hol

Reputation: 8423

You can use the round operator or simply format it if it really is output only.

round  ( value/divisor , 2)
to_char( value/divisor ,'99,999,999,990.99')

Please remark the 0 before the decimal point. This makes a value below 1 look more pretty with a leading zero. eg. 0.55 instead of .55.

Example SQL Fiddle

create table test (dividend       number, 
                   divisor        number, 
                   result         number,
                   result_rounded number);

insert into test values (100,10,null,null);
insert into test values (9,5,null,null);
insert into test values (10,15,null,null);

update test set  result         = dividend / divisor
                ,result_rounded = round(dividend/divisor,2); 

select * from test;

Result:

    DIVIDEND   DIVISOR     RESULT           RESULT_ROUNDED
    100        10          10               10
    9          5           1.8              1.8
    10         15          0.666666666667   0.67

But at the end when you try to output this then the formatting comes into play and the rounding does not make much difference.

Example SQL Fiddle

select to_char(result,'99,999,999,990.99'),
       to_char(result_rounded,'99,999,999,990.99') 
from test;

Result 

10.00    10.00
1.80     1.80
0.67     0.67

Upvotes: 5

codingbiz
codingbiz

Reputation: 26386

Try this 

SELECT TO_CHAR(1,'99,999,999,999.99') FROM DUAL;  -- THIS GIVES 1.OO

SELECT TO_CHAR(1.3,'99,999,999,999.99') FROM DUAL;  -- THIS GIVES 1.30

The repeated 99999 is just for BILLION formatting. For thousands, it should be 999,999.99

Upvotes: 1

Related Questions