Skip to content
Advertisement

Oracle how to truncate a decimal to 1 significant digit?

I would like to round decimal numbers down to the first significant digit. Since they will all have a varying number of leading zero decimals, i searched this site and found the following expression.

TRUNC(EPSILON,LENGTH(REGEXP_SUBSTR(EPSILON,'.0*')))

It works for all numbers that have less than 5 leading 0 decimal places, and does not return the right result when the number has 5 or more leading decimal places.

Edit: Epsilon is a calculated value from a query, the the data type of the values used in the calculation are Number.

See below for examples:

**EPSILON**     **Expected Result**     **Actual Result**
0.0000083333    0.000008                0
0.0000047666    0.000004                0
0.000025        0.00002                 0.00002
0.000012195     0.00001                 0.00001
0.00002         0.00002                 0.00002
0.00036333334   0.0003                  0.0003

Any help would be greatly appreciated! thanks!

Advertisement

Answer

Your code should be close to do what you want. I would just suggest modifying the regexp so it takes into account numbers on the left side of the dot:

TRUNC(EPSILON,LENGTH(REGEXP_SUBSTR(EPSILON,'^d*.0*')))
9 People found this is helpful
Advertisement