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!

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*')))

Advertisement

Answer