Help with numerical convertions + SQL querys

pedroteixeira07

Hello,

I have a question that is probably silly but I don't know how to do it.

Is it possible in a SQL query to search for approximate numbers?

For example if in a query I search for a decimal 19,90732500 will the database recognize a number like 16,907326? Or the WHERE X = '19,90732500' has to be exactlly the same?

How can I make this work with PHP ? create for example in the query a small range on which the values in between will be searched in the picture?

It's just because with all the automatic conversions, the numbers never stays quite the same. And in the end I'm searching for a number that doesn't exist anymore because it was approximated.

NogDog

You can apply ROUND() to both the value and column and compare them, assuming your round both to the same number of decimal positions.

johanafm

As I understand it, he wants to compare decimals only

$num = 16.907326;

$decimals = $num - (int) $num;
$where = 'WHERE (X - TRUNCATE(X, 0)) = '.$decimals';

But, exact comparisons of non exactly represented numbers may lead to errors. From MySQL manual

Because floating-point values are approximate and not stored as exact values, attempts to treat them as exact in comparisons may lead to problems.

NogDog

johanafm;10957361 wrote:
As I understand it, he wants to compare decimals only...

I was assuming that was a typo in the original post, but you never know. 🙂

PS: Assuming I was correct, for efficiency I might define the column as DECIMAL with the desired degree of accuracy for the decimal part, so that it does not have to be round()-ed in the query, allowing the query to be optimized via an index on that column.