Q
Problem solve Get help with specific problems with your technologies, process and projects.

Using the Oracle CEIL function to round to nearest five thousandth

The Oracle CEIL function can be used to round to the nearest five thousandth. Learn how to do this in this tip from SQL expert Karen Morton.

Rudy Limeback answered a question about using the SQL ceiling function to round to the nearest thousandth. Do you have a solution for rounding to the nearest five thousandth? For example, if the starting number is 21,489.35 the result would be 25,000. If the number is 25,000.01, the result would be 30,000. Thanks.

I believe Rudy's answer was for SQL Server, but the Oracle CEIL function would work similarly. As Rudy demonstrated in his answer to round to the nearest thousandth, here's the same example using the Oracle CEIL function:

 

 

SQL>select mynumber
  2       , mynumber/1000.0               as dividedby1000
  3       , ceil(mynumber/1000.0)      as ceilingresult
  4       , ceil(mynumber/1000.0)*1000 as myfinalnumber
  5    from mynumbers;

       MYNUMBER   DIVIDEDBY1000   CEILINGRESULT   MYFINALNUMBER
--------------- --------------- --------------- ---------------
            349            .349               1            1000
            999            .999               1            1000
           1000               1               1            1000
           1001           1.001               2            2000
           4590            4.59               5            5000
           6659           6.659               7            7000
          10432          10.432              11           11000

7 rows selected.
To change it to round to the nearest five thousandth, you simply change the divisor from 1000 to 5000 as follows:

SQL>select mynumber
  2       , mynumber/5000.0               as dividedby1000
  3       , ceil(mynumber/5000.0)      as ceilingresult
  4       , ceil(mynumber/5000.0)*5000 as myfinalnumber
  5    from mynumbers;

       MYNUMBER   DIVIDEDBY1000   CEILINGRESULT   MYFINALNUMBER
--------------- --------------- --------------- ---------------
            349           .0698               1            5000
            999           .1998               1            5000
           1000              .2               1            5000
           1001           .2002               1            5000
           4590            .918               1            5000
           6659          1.3318               2           10000
          10432          2.0864               3           15000

7 rows selected.

By the way, if you want to try this specific example yourself, here's the create table and a few inserts to build a test table.

create table mynumbers (mynumber number) ;
insert into mynumbers values (349);
insert into mynumbers values (999);
insert into mynumbers values (1000);
insert into mynumbers values (1001);
insert into mynumbers values (4590);
insert into mynumbers values (6659);
insert into mynumbers values (10432);
commit ;

This was last published in April 2010

Dig Deeper on Oracle and SQL

Have a question for an expert?

Please add a title for your question

Get answers from a TechTarget expert on whatever's puzzling you.

You will be able to add details on the next page.

Start the conversation

Send me notifications when other members comment.

Please create a username to comment.

-ADS BY GOOGLE

SearchDataManagement

SearchBusinessAnalytics

SearchSAP

SearchSQLServer

TheServerSide.com

SearchDataCenter

SearchContentManagement

SearchHRSoftware

Close