Retrieving truncated record from mysql database

Question

Say you have a string 3.4564.

A sample set in the database has these records(VARCHAR):

Name       Score

Peter      3.35643294332
John       3.47870923
James      3.740249842
Henry      4.0432849
Solomon    3.456183923
Andrew     3.45743

You want to truncate 3.4564 to 3 decimal places(to become 3.456) then compare with the values of the database that when rounded to three decimal places will give the same value as 3.456 i.e. retrieve the name 'Solomon'

What is the best way to go about it in php mysql?

Answer 1

If your score columns contains data stored as varchar, you could convert those to decimal, using the CAST function. For instance :

mysql> select cast('3.35643294332' as decimal(10,3));
+----------------------------------------+
| cast('3.35643294332' as decimal(10,3)) |
+----------------------------------------+
|                                  3.356 |
+----------------------------------------+
1 row in set (0,01 sec)

And note this will round the values correctly :

mysql> select cast('3.35663294332' as decimal(10,3));
+----------------------------------------+
| cast('3.35663294332' as decimal(10,3)) |
+----------------------------------------+
|                                  3.357 |
+----------------------------------------+
1 row in set (0,00 sec)

i.e. :

• '3.3564' was casted to 3.356
• and '3.3566' was casted to 3.357

Now, you only have to use that cast function in your comparisons in your where clause.

I suppose something like this should work, for instance :

select * 
from your_table
where cast(Score as decimal(10,3)) = cast('3.4564' as decimal(10,3))

i.e., you convert both the "input" and Score to decimals with 3 decimals ; and you convert those values.

A a sidenote : doing so, you won't be using any index that you might have on Score, and will always end up with a full-scan... Which means another solution would be better... For instance, if you could use a where clause like score >= X and Score <= Y, it would be much better...

But, as Score is stored as varchar, it won't be that easy, I suppose -- you should store those as decimals, btw...