CODEWITHSUNDEEP
sqloracle-databaserounding
kten
kten

Reputation: 455

Rounding to first digit +1 in oracle

I have a column as below and the output i want is as given below this example. Col1 is a numeric datatype.

Col1                OutputCol1
1234    round to    2000
2300000 round to    3000000
456789.23 round to  500000

Always first digit+1. I could use the round function with negative value but it rounds to lower if the second number is less than 5.

Upvotes: 0

Views: 273

Answers (3)

Gordon Linoff
Gordon Linoff

Reputation: 1270993

Well . . . you can add 1 to the first digit and then pad out with zeroes:

select rpad(cast(substr(col1, 1, 1) as int) + 1,
            log(10, col1),
            '0'
           )

Upvotes: 1

Thorsten Kettner
Thorsten Kettner

Reputation: 95090

Use string maipulation. Take the first digit, add one, add trailing zeros. In case a digit is followed by zeros only (1000 or 10.00) don't add 1.

select col1, 
  case when nvl(to_number(substr(to_char(col1), 2)),0) = 0 then
    to_number(rpad(substr(to_char(trunc(col1)), 1, 1), length(to_char(trunc(col1))), '0'))
  else
    to_number(rpad(to_char(to_number(substr(to_char(trunc(col1)), 1, 1)) + 1), length(to_char(trunc(col1))), '0'))
  end as x,
  to_number(substr(to_char(trunc(col1)), 2))
from

Upvotes: 1

Boneist
Boneist

Reputation: 23588

Here's a couple of ways - one mathematical, the other using manipulation of the string output of col1 in scientific notation:

WITH sample_data AS (SELECT 1234 col1 FROM dual UNION ALL
                     SELECT 2300000 col1 FROM dual UNION ALL
                     SELECT 456789.23 col1 FROM dual UNION ALL
                     SELECT -183 col1 FROM dual UNION ALL
                     SELECT 1000 col1 FROM dual UNION ALL
                     SELECT 0.392 col1 FROM dual)
SELECT col1,
       ceil(col1 / order_of_magnitude_of_col1) * order_of_magnitude_of_col1 output1,
       CEIL(to_number(SUBSTR(sn, 1, 4))) * POWER(10, to_number(SUBSTR(sn, 6))) output2
FROM   (SELECT col1,
               power(10, floor(LOG(10, abs(col1)))) order_of_magnitude_of_col1,
               to_char(col1, 'fms0.0EEEE') sn
        FROM   sample_data);

      COL1    OUTPUT1    OUTPUT2
---------- ---------- ----------
      1234       2000       2000
   2300000    3000000    3000000
 456789.23     500000     500000
      -183       -100       -100
      1000       1000       1000
     0.392        0.4        0.4

Upvotes: 0

Related Questions