A web developer's blog. PHP, MySQL, CakePHP, Zend Framework, Wordpress, Code Igniter, Django, Python, CSS, Javascript, jQuery, Knockout.js, and other web development topics.

Type casting in MySQL: Comparing string numbers and getting the right result

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

More info for the MySQL CAST() function here.

This entry was posted in General and tagged , . Bookmark the permalink.

3 Responses to Type casting in MySQL: Comparing string numbers and getting the right result

  1. k says:

    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??

  2. Wenbert says:

    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?

  3. Dinesh says:

    Thanks a lot, i was looking for same and was not getting any solution, finally got answer , thanks

Leave a Reply

Your email address will not be published. Required fields are marked *

You may use these HTML tags and attributes: <a href="" title=""> <abbr title=""> <acronym title=""> <b> <blockquote cite=""> <cite> <code> <del datetime=""> <em> <i> <q cite=""> <strike> <strong>