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