November 2018

2018-11-28

Get Significant Figures in Oracle

Basic Idea

You can use common logarithm (i.e. log base 10) to evaluate the number of digits.
FLOOR(LOG(10, n)) indicates the decimal places of the first non-zero digit as follows.

SQL> WITH DATA AS (
  2  SELECT 1234 N FROM DUAL
  3  UNION ALL SELECT 234 N FROM DUAL
  4  UNION ALL SELECT 34 N FROM DUAL
  5  UNION ALL SELECT 4 N FROM DUAL
  6  --UNION ALL SELECT 0 N FROM DUAL
  7  UNION ALL SELECT 0.123 N FROM DUAL
  8  UNION ALL SELECT 0.0234 N FROM DUAL
  9  UNION ALL SELECT 0.00345 N FROM DUAL
 10  )
 11  SELECT N, FLOOR(LOG(10, N)) FROM DATA;

         N FLOOR(LOG(10,N))
---------- ----------------
      1234                3
       234                2
        34                1
         4                0
      .123               -1
     .0234               -2
    .00345               -3

Note: LOG(10, 0) causes an error: ORA-01428: argument '0' is out of range

Therefore, you can get the value rounded to the first digit of a given number n by ROUND(n, - FLOOR(LOG(10, n))).

SQL> WITH DATA AS (
  2  SELECT 1234 N FROM DUAL
  3  UNION ALL SELECT 234 N FROM DUAL
  4  UNION ALL SELECT 34 N FROM DUAL
  5  UNION ALL SELECT 4 N FROM DUAL
  6  --UNION ALL SELECT 0 N FROM DUAL
  7  UNION ALL SELECT 0.123 N FROM DUAL
  8  UNION ALL SELECT 0.0234 N FROM DUAL
  9  UNION ALL SELECT 0.00345 N FROM DUAL
 10  )
 11  SELECT N, ROUND(n, - FLOOR(LOG(10, N))) FROM DATA;

         N ROUND(N,-FLOOR(LOG(10,N)))
---------- --------------------------
      1234                       1000
       234                        200
        34                         30
         4                          4
      .123                         .1
     .0234                        .02
    .00345                       .003

In order to round to first d digits, you can modify as ROUND(n, d - FLOOR(LOG(10, n)) - 1).

Function

These can be summarized as the following function.

CREATE OR REPLACE FUNCTION SIGNIFICANT_FIGURES(
    n   NUMBER,
    d   NUMBER
) RETURN NUMBER
IS
BEGIN
    IF n = 0 THEN
        RETURN 0;
    ELSE
        RETURN ROUND(n, d - FLOOR(LOG(10, n)) - 1);
    END IF;
END;

If you want to round half to even, use ROUND_HALF_EVEN function instead of the standard ROUND function.
Note: From Oracle Database 18c, you can use ROUND_TIES_TO_EVEN function.

2018-11-27

Round Half to Even in Oracle

Oracle standard ROUND function is rounding away from 0.
In order to round half to even (aka bankers’ rounding) in Oracle, you have to create a custom function.

Basic idea

Consider the case of rounding to an integer.

To judge whether the number is half-way between two integers, you have to divide the absolute value by 1 and see the remainder.
If the remainder is 0.5, you need to round to even. Otherwise, you can use the standard ROUND function
Actually, you had better divide the absolute value by 2 rather than 1 in order to round half to even.
In this case, you need to round to even when the remainder is 0.5 or 1.5.
When the remainder is 0.5, you have to round towards 0 (i.e. truncate).
When the remainder is 1.5, you have to round away from 0. (You can use the standard ROUND function.)

Function

These ideas can be summarized as the following function.

CREATE OR REPLACE FUNCTION ROUND_HALF_EVEN(
    n       NUMBER,
    integer NUMBER DEFAULT 0
) RETURN NUMBER
IS
BEGIN
    IF MOD(ABS(n) * POWER(10, integer), 2) = 0.5 THEN
        RETURN TRUNC(n, integer);
    ELSE
        RETURN ROUND(n, integer);
    END IF;
END;