[Trick] MySQL – Order string as number

Sometimes you have to store numbers in text format. This is not a good practice, but sometimes there is no other option.

When you perform an “order by” this field,you can get something like this:

1
10
11
111
12
2
20
3




The trick is to add a zero to you field, on the “order by” clause.

select field from table order by field+0 asc

That’s it. As simple as this :)

8 thoughts on “[Trick] MySQL – Order string as number”

  1. Hi,
    I need to order numbers with commas, ex:
    500,000
    400,000
    1,000,000

    if I perform “order by field+0 asc” I get the 1,000,000 as the first value. Any run-around on this?

    1. Hi.

      When you have 1,000,000 you have two commas, one for the thousands separator and one for the decimal separator.

      In fact when you add zero to the “string” it is cast to number. Since you have an invalid number, with two commas, it will remain a string.

      Try to fix it first by changing the decimal separator to a dot.

      Regards ;)

Leave a Reply

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

CAPTCHA Image

*

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>