Wish to select rows with number fields having a certain amount of data after the decimal


select number from table where number.scale > 4;

Is this possible to do directly, or must I cast to string and parse character wise

  • 1
    What version of Oracle are you running? And what are you trying to do? Commented Jan 22, 2012 at 4:57
  • 1
    Note that, scientifically speaking, 2.00 is very different from 2. The first number has a higher precision than the second, indicating more specific information about the quantity it measures. It means that you were able to measure down to hundredths of a value, instead of just to whole values. So from this perspective, saying you want to look for values with "data after the decimal" does't make sense because everything after the decimal -- even if it's just a .00 -- is data. Commented Jan 22, 2012 at 5:09

1 Answer 1


If I understand the question correctly you want to find numbers that have, for example, more than 4 digits of precision after the decimal? E.g., you want 3.0671, 3.06713, etc. but not 3.067 or 3.06 or 3. If that is the case then you could do something like:

select number from table where number - trunc(number,4) <> 0;

trunc here will simply remove everything ater the fourth decimal digi

(I'm not taking into consideration what Nick said regarding 2 and 2.00.)

  • 3
    I think a note is needed: Only use such checks with fixed point numbers, never with floating point ones. Commented Jan 22, 2012 at 10:30

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

Not the answer you're looking for? Browse other questions tagged or ask your own question.