This maybe an easy one but i couldn't get answer. I need to select float value from table
example table :-
value
10.2
4.5
4.6
4.06
my query
SELECT * FROM table where value = '4.6'
returns empty result set
how can i solve this !
-
possible duplicate of Selecting a float in MySQLJohan– Johan2011-05-26 08:25:07 +00:00Commented May 26, 2011 at 8:25
Add a comment
|
5 Answers
Generally, you should never check equality with floats (unless, potentially, you have the same object). Internally, it is represented with more precision, even if it isn't showing it to you by the time it outputs to the screen. This basic tenet holds true for computing in general.
There are a dozens of schemes for doing this, but here is a simple one, which should make sense:
SELECT * FROM table where value BETWEEN 4.599 AND 4.601
3 Comments
Vijay Kumar Kanta
Very useful solution. Between value-0.5 and value+0.5 is cool
plong0
Any idea on the precision floats are stored with? I am doing BETWEEN (value-0.000001) AND (value+0.000001) ... is that too limiting?
John Green
The issue isn't really with precision, but with the math that is used to generate the float in the first place. A fixed epsilon (variance) is fine in many cases, but very large (or very small) floating point numbers will skew the importance of the precision. Plus, you'll actually have variances between microprocessors and even between different C compilers, so there's no real magical / safe solution, especially in 4GL language like SQL. That being said, if your numbers are generally in the same kind of range, you should be fine with the kind of fixed/absolute epsilon you illustrate above.
Use decimal instead of float.
A decimal(10,2) will have 2 and only 2 decimal places and can be compared in the same manner as integers. Especially for monetairy values you should always use decimal, but anywhere where rounding errors are unwanted, decimal is a good choice.
See: http://dev.mysql.com/doc/refman/5.1/en/precision-math-decimal-changes.html
Comments
Today, I also came across the same situation and get resolved just by using FORMAT function of MySQL, It will return the results that exactly match your WHERE clause.
SELECT * FROM yourtable WHERE FORMAT(`col`,2) = FORMAT(value,2)
Explanation:
FORMAT('col name',precision of floating point number)
Hope it helps.
2 Comments
Gabriel Reguly
This helps when one can't change the column type from float to decimal as the approved answer suggests.
Biju P Dais
Helped me because I could not change the datatype of the original column.
You can also try query
SELECT * FROM table WHERE value LIKE 4.6;
2 Comments
Joshua Pinter
You're the hero.
QuantumBlack
This will still fail in a lot of cases due to leading zeroes.
LIKE 4.6 won't match "4.60", LIKE 4.600 won't match it eitheryou write:
SELECT * FROM table where round(value, 1) = 4.6
