CODEWITHSUNDEEP
mysqlfloating-point
Joseph
Joseph

Reputation: 25513

Check for equality on a MySQL Float field

I have a Joomla system and I'm trying to change the search so that it correctly finds floating point values in the database.

So, I have a query that is built at runtime that looks something like this:

select 'column1'
from 'some_table'
where 'some_float_field' <=> '2.18'

This doesn't work, it never matches anything, even though I see records in the db with this value there.

So I tried to just do this instead:

select 'column1'
from 'some_table'
where 'some_float_field' <=> 2.18

No luck, so then I tried casting to a decimal (float doesn't work for some reason), so I tried this:

select 'column1'
from 'some_table'
where 'some_float_field' <=> CAST('2.18' AS DECIMAL(20, 2))

No dice...

Keep in mind that >= or <= returns the proper results, just <=> gives me issues.

How do I get equality to work here?

Upvotes: 25

Views: 24930

Answers (5)

Seva Alekseyev
Seva Alekseyev

Reputation: 61341

Consider this:

where abs(some_float_field - 2.18) < 0.001

Upvotes: 3

Neeraj
Neeraj

Reputation: 702

Here is an easy way to do this

Just write where some_float_field LIKE 2.18

Hope it will help ;)

Upvotes: 21

Shaolin
Shaolin

Reputation: 434

I know these is an old post but if you don't want a precise comparison just use LIKE or NOT LIKE :

select 'column1'
from 'some_table'
where 'some_float_field' NOT LIKE '2.18'

Upvotes: 1

Joseph
Joseph

Reputation: 25513

I found a way to check for what my users view as equality.

I had to convert the field to a character string and test the character sets, so this works fine for them:

select 'column1'
from 'some_table'
where CAST('some_float_field' AS CHAR) <=> '2.18'

Upvotes: 5

dcp
dcp

Reputation: 55434

Usually with these types of questions it's good to provide a small example to replicate your results.

Usually testing for exact float values is a bad idea since floating point precision isn't an exact science. It's much better to use some tolerance.

create table foo1 (col1 float);

insert into foo1 values (2.18);
select * from foo1 where abs(col1-2.18) <= 1e-6

Upvotes: 18

Related Questions