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...
Continue Reading This Article
Enjoy this article as well as all of our content, including E-Guides, news, tips and more.
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 ;
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.
Meet all of our Oracle Database / Applications experts
View all Oracle Database / Applications questions and answers
Start the conversation
0 comments