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
-
1What version of Oracle are you running? And what are you trying to do?– Nick ChammasCommented Jan 22, 2012 at 4:57
-
1Note 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 ChammasCommented Jan 22, 2012 at 5:09
Add a comment
|
1 Answer
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.)
answered Jan 22, 2012 at 9:04
-
3I 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