CODEWITHSUNDEEP
phpmysqlfloating-pointdatabase
Meep3D
Meep3D

Reputation: 3756

Selecting a float in MySQL

I am trying to do a SELECT match on a table based upon an identifier and a price, such as:

SELECT * FROM `table` WHERE `ident`='ident23' AND `price`='101.31';

The above returns zero rows, while if you remove the price='101.31' bit it returns the correct row.

Doing a...

SELECT * FROM `table`;

Returns the same row as above and quite clearly states that price='101.31'. Yet select fails to match it. Changing = to <= makes it work - but this is not exactly a solution.

Is there a way of casting the MySQL float to 2 digits before the operation is performed on it, thus making the above SELECT work (or some other solution)?

Thanks!

Upvotes: 30

Views: 60440

Answers (8)

Gerson Diniz
Gerson Diniz

Reputation: 1279

Try this: SELECT * FROM table WHERE price like 101.31;

Upvotes: 4

Emmanuel Ifebuchechukwu Ossai
Emmanuel Ifebuchechukwu Ossai

Reputation: 1

I was searching for solution but finally I did it using my previous php number_format() which came in seeing @hmasif's solution.

Use this and you'll get your float - mysql match:

$floatmatch = number_format((float)$yourfloatvariable,5);

where 5 is five strings from decimal point. e.g. 7.93643

Upvotes: 0

hmasif
hmasif

Reputation: 191

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)

Upvotes: 9

Mark L
Mark L

Reputation: 13425

Does this work?

SELECT * , ROUND( price, 2 ) rounded
FROM table
WHERE ident = 'ident23'
HAVING rounded = 101.31

Upvotes: -1

jcdyer
jcdyer

Reputation: 19145

Don't ever use floats for money.

Upvotes: 12

Matt Solnit
Matt Solnit

Reputation: 33534

Casting to a decimal worked for me:

SELECT * FROM table WHERE CAST(price AS DECIMAL) = CAST(101.31 AS DECIMAL);

However, you may want to consider just making the price column a DECIMAL in the first place. DECIMAL is generally considered to be the best type to use when dealing with monetary values.

Upvotes: 52

Eric Petroelje
Eric Petroelje

Reputation: 60498

It doesn't work because a float is inherently imprecise. The actual value is probably something like '101.3100000000001' You could use ROUND() on it first to round it to 2 places, or better yet use a DECIMAL type instead of a float.

Upvotes: 12

LorenVS
LorenVS

Reputation: 12857

Perhaps something along these lines:

SELECT * FROM table WHERE ident='ident23' AND ABS(price - 101.31) < .01;

Upvotes: -1

Related Questions