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 -3Note: 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 .003In 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.
yabtb.blogspot.com
