Take the 2-minute tour ×
Database Administrators Stack Exchange is a question and answer site for database professionals who wish to improve their database skills and learn from others in the community. It's 100% free, no registration required.

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

eg

select number from table where number.scale > 4;

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

share|improve this question
1  
What version of Oracle are you running? And what are you trying to do? –  Nick Chammas Jan 22 '12 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. –  Nick Chammas Jan 22 '12 at 5:09
add comment

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.)

share|improve this answer
3  
I think a note is needed: Only use such checks with fixed point numbers, never with floating point ones. –  ypercube Jan 22 '12 at 10:30
add comment

Your Answer

 
discard

By posting your answer, you agree to the privacy policy and terms of service.

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