Using the Oracle CEIL function to round to nearest five thousandth

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 ;