Skip to Main Content
  • Questions

  • Is there a way to convert the 0.12 to 0.15 , 0.16 to 0.20 etc decimals

Breadcrumb

Question and Answer

Tom Kyte

Thanks for the question, prashanth.

Asked: October 09, 2001 - 9:04 am UTC

Last updated: October 09, 2008 - 2:31 pm UTC

Version: 7.3

Viewed 1000+ times

You Asked

Hi

I have the following data

1.11
0.11
0.99
0.14

I need to round this value to the following values

1.15
0.15
1.00
0.15

can you help me out in getting a solution for the same.

Thanks in advance.

and Tom said...

something like:

ops$tkyte@ORA817DEV.US.ORACLE.COM> create table t ( x number );

Table created.

ops$tkyte@ORA817DEV.US.ORACLE.COM>
ops$tkyte@ORA817DEV.US.ORACLE.COM> insert into t values ( 1.11 );

1 row created.

ops$tkyte@ORA817DEV.US.ORACLE.COM> insert into t values ( 0.11 );

1 row created.

ops$tkyte@ORA817DEV.US.ORACLE.COM> insert into t values ( 0.99 );

1 row created.

ops$tkyte@ORA817DEV.US.ORACLE.COM> insert into t values ( 0.14 );

1 row created.

ops$tkyte@ORA817DEV.US.ORACLE.COM>
ops$tkyte@ORA817DEV.US.ORACLE.COM> begin
2 for i in 0..99
3 loop
4 insert into t values ( 1 + i/100 );
5 end loop;
6 end;
7 /

PL/SQL procedure successfully completed.

ops$tkyte@ORA817DEV.US.ORACLE.COM> column converted format 0.99
ops$tkyte@ORA817DEV.US.ORACLE.COM>
ops$tkyte@ORA817DEV.US.ORACLE.COM> select x, x-trunc(x),
trunc(x)+(ceil( (x-trunc(x))*100/5 )*5)/100 converted from t;

X X-TRUNC(X) CONVERTED
---------- ---------- ---------
1.11 .11 1.15
.11 .11 0.15
.99 .99 1.00
.14 .14 0.15
1 0 1.00
1.01 .01 1.05
1.02 .02 1.05
1.03 .03 1.05
1.04 .04 1.05
1.05 .05 1.05
1.06 .06 1.10
1.07 .07 1.10
1.08 .08 1.10
1.09 .09 1.10
1.1 .1 1.10
1.11 .11 1.15

will do it.

Rating

  (14 ratings)

Is this answer out of date? If it is, please let us know via a Comment

Comments

Related To this Rounding i have one similar problem

IB, June 30, 2002 - 12:35 am UTC


If i give
Select Round((204.27)*35/100,2) From Dual;

Which Returns

71.49

But if you calculate manually it comes as 71.4945

So the Result should be 71.50

Simply to say i wanted to Round Up but oracle function Round(n,m) where m=2 returns like 5/4 electronic calculator rounding .Is there any way to do like above(tht is to get the result 71.50 by using oracle function).

Anybody has any suggessions .Pls let me know.

Regards,
IB From India.



Tom Kyte
June 30, 2002 - 9:56 am UTC

Umm, look at your data again.

You asked "please round to the second decimal place". You give us input:

71.4945

will, .494 rounds to .49 doesn't it? (yes, yes it does).

Why would you believe it should be .50???? That doesn't make sense to me (it'll go to .5 which is .50 if you round( x, 1 ) -- to a SINGLE decimal point.

This is behaviing correctly, 100%

Round not always returns the same

HOUMAN, July 21, 2005 - 4:42 pm UTC 

Hi Tom

I have the following strange behavior of round function.
In my first query the round doesn't round properly:
SQL>   Select (rec_qty*rec_unit_value) AS "is", round((rec_qty*rec_unit_value),2) as QTY_TIMES_VALUE
,
  2   rec_tot_cost,round((rec_qty*rec_unit_value),2)- rec_tot_cost as
  3    DIFFERENCE
  4    from rec
  5    where  REC_ID=14546
  8    order by rec_time desc;

QTY_TIMES_VALUE REC_TOT_COST DIFFERENCE
---------- --------------- ------------ ----------
 36343.595        36343.59      36343.6       -.01

I think REC_TOT_COST should be 36343.6. But the following query returns correctly :

SQL>  select round(36343.595,2) as dec from gts;

       DEC
----------
   36343.6

which behavior is correct?

Tom Kyte
July 21, 2005 - 5:48 pm UTC

i would need the original inputs. it is sort of hard to tell what I'm looking at here.

A reader, July 21, 2005 - 11:33 pm UTC

Hi

How to display 0.X on the web?It always displays .X missing 0
The column type is number(n,m)
I found that PL/SQL sends .X to JAVA.

Thanks in advance.

Tom Kyte
July 22, 2005 - 8:48 am UTC

er?


plsql sends to java only that which java (which is just a client, like sqlplus no more- no less, plsql doesn't even know "java" is the client) asked to be sent.


if you want pretty formatted numbers, you to_char them

select ..., to_char( num_field, '999,990.9' ) num_str, ......

for example.

follow up question

Houman, July 24, 2005 - 11:31 pm UTC 


These are my orginal inputs 


CREATE TABLE RND (REC_QTY NUMBER(5) NOT NULL,REC_UNIT_VALUE NUMBER NOT NULL,REC_TOT_COST NUMBER(20,4));
INSERT INTO RND VALUES (49279,0.737506747,36343.6);


In my first query the round doesn't round properly:
SQL>   Select (rec_qty*rec_unit_value) AS "is", 
round((rec_qty*rec_unit_value),2) as QTY_TIMES_VALUE,
  2   rec_tot_cost,round((rec_qty*rec_unit_value),2)- rec_tot_cost as
  3    DIFFERENCE
  4    from rnd;

QTY_TIMES_VALUE REC_TOT_COST DIFFERENCE
---------- --------------- ------------ ----------
 36343.595        36343.59      36343.6       -.01

I think REC_TOT_COST should be 36343.6. But the following query returns 
correctly :

SQL>  select round(36343.595,2) as dec from gts;

       DEC
----------
   36343.6

which behavior is correct?

Tom Kyte
July 25, 2005 - 7:22 am UTC 

see, you are still not showing the output from the query.  

ops$tkyte-ORA10G> set numformat 999999.99999999999
ops$tkyte-ORA10G> Select rec_qty,
  2         rec_unit_value,
  3             (rec_qty*rec_unit_value) AS "is",
  4         round((rec_qty*rec_unit_value),2) as QTY_TIMES_VALUE,
  5         rec_tot_cost,
  6         round((rec_qty*rec_unit_value),2)- rec_tot_cost as DIFFERENCE
  7    from rnd;
 
            REC_QTY      REC_UNIT_VALUE                  is
------------------- ------------------- -------------------
    QTY_TIMES_VALUE        REC_TOT_COST          DIFFERENCE
------------------- ------------------- -------------------
  49279.00000000000        .73750674700   36343.59498541300
  36343.59000000000   36343.60000000000       -.01000000000
 


I see nothing wrong with these numbers.  

round((rec_qty*rec_unit_value),2) as QTY_TIMES_VALUE  is 
  36343.59000000000

and
rec_tot_cost is
36343.60000000000



qty_times_values is not the number you show, the output you show did not come from the query you ran. (missing columns and everything)

Is there a way to convert the 0.12 to 0.15 , 0.16 to 0.20 etc decimals

Geo, August 04, 2005 - 10:58 am UTC

Your formula (trunc(x)+(ceil( (x-trunc(x))*100/5 )*5)/100) is too much complex; the following from the same result and it is very simpler

ceil((x * 20) ) / 20

Tom Kyte
August 04, 2005 - 12:06 pm UTC

sweet, thanks!

that is easier indeed.

round function

omer, August 05, 2005 - 3:34 am UTC

Hi!
i have to round data according to the following scenario.

create table omer_test
(num number(5,2));

insert into omer_test
values(100.80);

insert into omer_test
values(98.31);

insert into omer_test
values(85.15);

select *
from omer_test
/

NUM
-----
100.8
98.31
85.15

now i want to round this data that it should be

101
98.31 -- this will remain same
85.2

how this will be solve?

thanks

Tom Kyte
August 05, 2005 - 11:03 am UTC

you would have to EXPLAIN in some detail the "LOGIC" behind this magic.

(it appears to be arbitrary to me, I don't see a pattern)

Very cool - but how bout the other way

Stephan, June 23, 2007 - 10:38 pm UTC

Very cool - I never considered using ceil like that. Can it be adapted to handle rounding to the next 10?
eg:
         T          D
---------- ----------
      2.02         10
      24.2         30
      38.2         40
      76.5         80


What I had come up with own my own, which I can adapt to do either integer rounding as above, or the kind of decimal rounding you've been discussing, is:
var b1 number
exec :b1 := 10;
with data as
(select :b1*(l-1) d from (select level l from dual connect by level < 999))
select t, d from t, data where data.d =(select min(d) from data where d >= t.t);


So - are there other, perhaps easier ways to round up to the next nearest 10?

PS - even if there is, I might just stay with this - it is the example on which I figured out both the with data clause and connect by ;)
Tom Kyte
July 02, 2007 - 9:00 am UTC 

with data as
(select (level-1)/10 l from dual connect by level <= 102)
select l, (trunc(l/10)+decode(l/10,trunc(l/10),0,1))*10 from data
/

As usual

Stephan, July 02, 2007 - 7:33 pm UTC

As usual, Tom, you rock.

Thanks again.

Round question

Peter Ho, July 14, 2007 - 1:15 pm UTC

Hi Tom,

I have a simple query, and here is the output:

SUM(T1) ROUND(SUM(T1),2) ROUND(147.095,2)
147.095 147.09 147.1

Why the rounding result is different, even though it looks like they are using the same number to me?

Tom Kyte
July 17, 2007 - 10:34 am UTC

need your data to reproduce with - guess: your application is really getting a sum(t1) that has many more digits after the decimal and 147.095 is really "already rounded" to three digits...


ops$tkyte%ORA10GR2> create table t as select 147.0949999 x from dual;

Table created.

ops$tkyte%ORA10GR2>
ops$tkyte%ORA10GR2> select sum(x), round(sum(x),2), round(147.095,2) from t;

    SUM(X) ROUND(SUM(X),2) ROUND(147.095,2)
---------- --------------- ----------------
   147.095          147.09            147.1

ops$tkyte%ORA10GR2> set numformat 999.9999999999
ops$tkyte%ORA10GR2> select sum(x), round(sum(x),2), round(147.095,2) from t;

         SUM(X) ROUND(SUM(X),2) ROUND(147.095,2)
--------------- --------------- ----------------
 147.0949999000  147.0900000000   147.1000000000

Other way to round to the next 10

Mikel, September 04, 2007 - 6:38 am UTC 

Hi,

I have been reading this thread and, as allways, I learned something new, but this time I want to contribute.

I have created a table named "t" with a column "c" and inserted the data provided by Stephan and executed this SQL:

SQL> select c, c + 5, round(c + 5, -1) from t;

 
         C        C+5 ROUND(C+5,-1)
---------- ---------- -------------
      2,02       7,02            10
      24,2       29,2            30
      38,2       43,2            40
      76,5       81,5            80

Regards

Tom Kyte
September 05, 2007 - 1:45 pm UTC

forest for the trees time :)

thanks, that is better.

round to nearest 1/2

Steve, October 09, 2008 - 1:54 pm UTC

Is there an easy way to round to the nearest 1/2 number as opposed to just rounding up?
e.g. 1.11 to 1.0
1.25 to 1.5
5.6 to 5.5
2.75 to 3.0

I have this:
select case when 100.1 - trunc(100.1) < 0.25
            then floor(100.1 * 2) / 2
            when 100.1 - trunc(100.1) < 0.5
            then ceil(100.1 * 2) / 2
            when 100.1 - trunc(100.1) < 0.75
            then floor(100.1 * 2) / 2
            else ceil(100.1 * 2) / 2
       end as res
from dual

       RES
----------
       100


Which works, but is a bit awkward for where I will utimately be putting the query logic. Is there is a more efficient way to do this?

Thanks,
Steve
Tom Kyte
October 09, 2008 - 2:31 pm UTC

basically, take the trunc(x) and add to it:

0.00 <= x < 0.25 => +0
0.25 <= x < 0.75 => +0.5
0.75 <= x < 1.00 => +1.0




so, math:

ops$tkyte%ORA11GR1> create table t ( x number );
Table created.


ops$tkyte%ORA11GR1> insert into t select rownum *0.05 from all_users where rownum < 20;
19 rows created.

ops$tkyte%ORA11GR1> insert into t select x+0.04 from t;
19 rows created.

ops$tkyte%ORA11GR1> select x, ceil((x-0.25+0.001)*2)/2 from t order by x;

         X CEIL((X-0.25+0.001)*2)/2
---------- ------------------------
       .05                        0
...
       .24                        0
       .25                       .5
...
       .74                       .5
       .75                        1
...
       .99                        1

38 rows selected.


and a quick test

ops$tkyte%ORA11GR1> truncate table t;

Table truncated.

ops$tkyte%ORA11GR1> insert into t values ( 1.11 );

1 row created.

ops$tkyte%ORA11GR1> insert into t values ( 1.25 );

1 row created.

ops$tkyte%ORA11GR1> insert into t values ( 5.6 );

1 row created.

ops$tkyte%ORA11GR1> insert into t values ( 2.75 );

1 row created.

ops$tkyte%ORA11GR1>
ops$tkyte%ORA11GR1> select x, floor(x) + ceil(( (x-trunc(x)) - 0.25+0.001)*2)/2 from t order by x;

         X FLOOR(X)+CEIL(((X-TRUNC(X))-0.25+0.001)*2)/2
---------- --------------------------------------------
      1.11                                            1
      1.25                                          1.5
      2.75                                            3
       5.6                                          5.5


Now, doesn't do negatives - but you see the approach, you'd be able to add that if you wanted...

works great

Steve, October 10, 2008 - 9:37 am UTC

As usual, very nice.

Thanks,
Steve

Using a ROUND function

Martin Vajsar, October 10, 2008 - 10:21 am UTC

The same can be achieved more easily using a ROUND function. Actually, rounding to an inverse integer (1/n) is pretty straightforward (and it handles negative values as well):
insert into t select -x from t;

select x, round(2*x)/2 halves, round(4*x)/4 quarters from t order by x;

etc.

Excellent

Sathya, October 22, 2008 - 11:13 am UTC

Excellent Technical Knowledge Tom
more

Connor and Chris don't just spend all day on AskTOM. You can also catch regular content via Connor's blog and Chris's blog. Or if video is more your thing, check out Connor's channel and Chris's channel from their Youtube channels. And of course, keep up to date with AskTOM via the official twitter account.

More to Explore

PL/SQL demos

Check out more PL/SQL tutorials on our LiveSQL tool.

PL/SQL docs

PL/SQL reference manual from the Oracle documentation library