What is the syntax in Oracle to round any number to the greatest/highest place value of that number?

Question

I have a wide variety of numbers In the ten thousands, thousands, hundreds, etc

I would like to compute the rounding to the highest place value ex:

Starting #: 2555.5 Correctly Rounded : 3000

——

More examples ( in the same report )

Given: 255 Rounded: 300

Given: 25555 Rounded: 30000

Given: 2444 Rounded: 2000

But with the Round() or Ceil() functions I get the following

Given: 2555.5 Did not want : 2556

Any ideas ??? Thank you in advance

Do you need to handle the input value 0 (zero)? Do you need to handle negative numbers? How about numbers between 0 and 1 - for example 0.048, what is the desired result (if you must handle it)? Note that the heavily upvoted answer will error out on the first two examples (zero and negative numbers), and simply return 0 for the input 0.048, which is probably not what you need. — , Mar 06 '22 at 00:30

Olivier Jacot-Descombes · Answer 1 · 2022-03-04T12:36:20.213

You can combine numeric functions like this

SELECT
    col,
    ROUND(col / POWER(10,TRUNC(LOG(10, col)))) * POWER(10,TRUNC(LOG(10,col)))
FROM Data

See fiddle

Explanation:

LOG(10, number) gets the power you need to raise 10 to in order get the number. E.g., LOG(10, 255) = 2.40654 and 10^2.40654 = 255
TRUNC(LOG(10, col)) the number of digit without the leading digit (2).
POWER(10,TRUNC(LOG(10, col))) converts, e.g., 255 to 100.
Then we divide the number by this rounded number. E.g. for 255 we get 255 / 100 = 2.55.
Then we round. ROUND(2.55) = 3
Finally we multiply this rounded result again by the previous divisor: 3 * 100 = 300.

By using the Oracle ROUND function with a second parameter specifying the number of digits with a negative number of digits, we can simplify the select command (see fiddle)

SELECT
    col, 
    ROUND(col, -TRUNC(LOG(10, col))) AS rounded
FROM Data

You can also use this to round by other fractions like quarters of the main number:

ROUND(4 * col, -TRUNC(LOG(10, col))) / 4 AS quarters

see fiddle

Nice elegant solution. I'm a novice SQL developer so pardon my naivete but is there any reason why this all cant be wrapped into a function so its easier to call? — Pugzly, Mar 03 '22 at 18:24
Of course you can wrap `ROUND(col, -TRUNC(LOG(10, col)))` into a [Oracle Function](https://docs.oracle.com/cd/B19306_01/server.102/b14200/statements_5009.htm). — Olivier Jacot-Descombes, Mar 04 '22 at 12:34

score 1 · Answer 2 · answered Mar 03 '22 at 16:17

Similar to what Olivier had built, you can use a combination of functions to round the numbers as you need. I had built a similar method except instead of using LOG, I used LENGTH to get the number of non-decimal digits.

WITH
    nums (num)
    AS
        (SELECT 2555.5 FROM DUAL
         UNION ALL
         SELECT 255 FROM DUAL
         UNION ALL
         SELECT 25555 FROM DUAL
         UNION ALL
         SELECT 2444 FROM DUAL)
SELECT num,
       ROUND (num, (LENGTH (TRUNC (num)) - 1) * -1) as rounded
  FROM nums;



      NUM    ROUNDED
_________ __________
   2555.5       3000
      255        300
    25555      30000
     2444       2000

What is the syntax in Oracle to round any number to the greatest/highest place value of that number?

2 Answers2