CODEWITHSUNDEEP
sqloracle-database
dewijones92
dewijones92

Reputation: 1359

Rounding to a specific number of significant digits in Oracle

Is there a rounding function oracle to round to a specific number of significant digits?

For example, round 1278 to 1300, round to two significant digits.

Upvotes: 3

Views: 7233

Answers (5)

MT0
MT0

Reputation: 168740

If you want to round a number to a number of significant figures then use @Jeffery Kemp's comment (except you need to subtract 1 from the digits)

TO_NUMBER(TO_CHAR(value, '9.' || LPAD('9',digits - 1,'9') || 'EEEE'))

or @f3lix's answer can be fixed to work for 0 and negative numbers using:

ROUND(value, digits - CASE value WHEN 0 THEN 1 ELSE FLOOR(LOG(10, ABS(value))) + 1 END)

If you want to format the number to show a number of significant digits then you can take the rounded number and use TO_CHAR to format it and calculate how many digits should be in the integer- and decimal-parts of the format mask:

SELECT value,
       digits,
       ROUND(value, digits - CASE value WHEN 0 THEN 1 ELSE FLOOR(LOG(10, ABS(value))) + 1 END)
         AS rounded_value,
       TO_CHAR(
         ROUND(value, digits - CASE value WHEN 0 THEN 1 ELSE FLOOR(LOG(10, ABS(value))) + 1 END),
         'FM9'
         || LPAD('0', GREATEST(CASE value WHEN 0 THEN 1 ELSE FLOOR(LOG(10, ABS(value))) + 1 END, 1), '0')
         || CASE
            WHEN digits > CASE value WHEN 0 THEN 1 ELSE FLOOR(LOG(10, ABS(value))) + 1 END
            THEN '.' || LPAD('0', digits - CASE value WHEN 0 THEN 1 ELSE FLOOR(LOG(10, ABS(value))) + 1 END, '0')
            END
       ) AS formatted_value
FROM   table_name;

Which, for the sample data:

CREATE TABLE table_name (value, digits) AS
  SELECT 1278,       2 FROM DUAL UNION ALL 
  SELECT 1234.56789, 2 FROM DUAL UNION ALL 
  SELECT 1234.56789, 4 FROM DUAL UNION ALL 
  SELECT 1234.56789, 5 FROM DUAL UNION ALL 
  SELECT 1234.56789, 6 FROM DUAL UNION ALL 
  SELECT 1000,       2 FROM DUAL UNION ALL 
  SELECT 1000,       5 FROM DUAL UNION ALL 
  SELECT 0.1234,     2 FROM DUAL UNION ALL 
  SELECT 0.1234,     8 FROM DUAL UNION ALL 
  SELECT 3,          2 FROM DUAL UNION ALL 
  SELECT 3.12345,    2 FROM DUAL UNION ALL 
  SELECT 0,          2 FROM DUAL UNION ALL 
  SELECT -3.12345,   2 FROM DUAL UNION ALL 
  SELECT -1.2345e-5, 3 FROM DUAL UNION ALL 
  SELECT 99.997,     2 FROM DUAL

Outputs:

VALUE DIGITS ROUNDED_VALUE FORMATTED_VALUE
1278 2 1300 1300
1234.56789 2 1200 1200
1234.56789 4 1235 1235
1234.56789 5 1234.6 1234.6
1234.56789 6 1234.57 1234.57
1000 2 1000 1000
1000 5 1000 1000.0
.1234 2 .12 0.12
.1234 8 .1234 0.12340000
3 2 3 3.0
3.12345 2 3.1 3.1
0 2 0 0.0
-3.12345 2 -3.1 -3.1
-.000012345 3 -.0000123 -0.0000123
99.997 2 100 100

fiddle

Upvotes: 0

Camiel
Camiel

Reputation: 1

Or just: to_number(to_char(<your_number>,'0.0EEEE'),'0.0EEEE')

Upvotes: 0

f3lix
f3lix

Reputation: 29885

Try ROUND(x, d - FLOOR(LOG(10, x)) - 1) where d is the number of significant digits and x is the value to be rounded.

Example:

SELECT
  ROUND(x, d - FLOOR(LOG(10, x)) - 1) 
FROM (SELECT 1278 x, 2 d FROM DUAL UNION ALL 
      SELECT 0.1234 x, 2 d FROM DUAL  );

Upvotes: 4

Kirill Leontev
Kirill Leontev

Reputation: 10941

Just off the top of my head, probably a subject to further optimization :)

16:54:16 SYSTEM@dwal> select to_number(to_char(1278, '9.9EEEE'))  t from dual;

         T
----------
      1300

Elapsed: 00:00:00.01
16:54:34 SYSTEM@dwal> select to_number(to_char(.1234, '9.9EEEE'))  t from dual;

         T
----------
       .12

Elapsed: 00:00:00.01

Upvotes: 3

ivanatpr
ivanatpr

Reputation: 1880

select round(1348,-2) from dual;

Upvotes: -1

Related Questions