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

Answer 1

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

Answer 2

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