I recently ran into a problem where I had to compare “1000″ and “999″ — take note of the quotes, these values are strings. I did a SUBSTRING() in order to get those 2 strings, er “numbers”.
In this SQL statement, it asks whether “1000″ is greater than “999″. At first glance, the expected result would be ‘Yes’ but when you run that query, the result is ‘NO’. More info for MySQL If statements here.
SELECT IF("1000" > "999",'Yes','NO') FROM table WHERE 1
By doing a CAST() on the string, you will the correct result.
SELECT IF( CAST("1000" AS Decimal) > CAST("9" AS Decimal),'Yes','NO') FROM table WHERE 1
How do you get this to work up to 2 decimal points? I tried casting to Decimal, set it to 2 decimal points. The system drops the trailing zeros. SO – $1000.00 shows as $1000, $1000.50 shows as $1000.5 even though I cast to 2 decimal points. If I use Format, it makes it a string (which I don’t want). If I cast the format it drops everything after the comma. There is no VAL() in MySQL so I can’t try that. Any suggestions for Casting a Decimal to 2 decimals and have them show up when they are zero’s so that comparing works??
I have not tried casting it with 2 decimals points. But I think that your problem is the “output” of the SELECT statement? If so, I’m thinking that you could probably cast it to a string/text and format it with two decimal places.
SELECT FORMAT(12332.123456, 4) WHERE ....
Regarding after the comma, I guess that a quick and dirty way would be to strip those out and format it as decimal. You can use REPLACE: http://dev.mysql.com/doc/refman/5.0/en/string-functions.html#function_replace
In addition to comparing with decimals, 100.00 is still equal to 100.0 and 98.9 is still less than 99.9. I do not get the point if having exactly 2 decimal places when comparing two values. Or did I miss/misunderstood something?
Thanks a lot, i was looking for same and was not getting any solution, finally got answer , thanks