Sort VARCHAR as INT in MySQL query

If you need to sort varchar data as int, there is one very nice function in MySQL. Problem I had to solve was to sort values as below in a human logical way. Values were similar to this (and I wanted it to be sorted like below):

1; 2; 2a; 3; 4; 5; 5a; 5b; 5c; 6; 7; 11; 14; 14a; 16;

The problem was that if I put this data to MySQL database as varchar and sort by this column, MySQL returned it this way:

1; 11; 14; 14a; 16; 2; 2a; 3; 4; 5; 5a; 5b; 5c; 6; 7;

My second thought was to put is as INTEGER values... It would be sorted as it should but then I would loose values like

2a; 5a; 5b; 5c; 14a;

as it would be converted by MySQL to integer values (2; 5; 14;).

Then I have thought it would be best to put this data to database as a varchar values and sort it as int! After some googling I have found great function that have helped me! The solution was CAST function. To sort it the way I wanted I have just added to my MySQL query this sorting method (pic_number is my field with value to sort):

ORDER BY CAST(`pic_number` AS SIGNED)

and for reverse order

ORDER BY CAST(`pic_number` AS SIGNED) DESC

And it worked like a charm!

Function is as simple to use as:

CAST(expr AS TYPE)

Other possible conversion types you may need are:

  • BINARY[(N)]
  • CHAR[(N)]
  • DATE
  • DATETIME
  • DECIMAL[(M[,D])]
  • SIGNED [INTEGER]
  • TIME
  • UNSIGNED [INTEGER]

You may read more about this types in MySQL docs here. This function saved me a lot of coding in PHP. I hope it have helped you as much as it helped me!

Leave a Reply

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

This site uses Akismet to reduce spam. Learn how your comment data is processed.

  12. Another way I found to do it was this way,

    ORDER BY (0+var_char_field)

    which forces it to evaluate a varchar field as a number.

    Any advice comparing these two methods? Any problems with either method?

    Reply

  14. Hi Thanks for a wonderful solution. But when I am trying to sort values in following order like: 10.50, 10.00, 10.00, 9.50, 9.00 this solution breaks and provides data in below order:
    10.00, 10.00, 10.50 and so on. Is there any way by which I can also sort in decimal places?

    Reply

  16. Not working

    Table: tbl_contact
    Name(VARCHAR 20) Position(VARCHAR 5)
    Kathirason 02
    Asokan 03
    Techispeaks 01
    Pro 100
    Ken 50

    Output:
    Name(VARCHAR 20) Position(VARCHAR 5)
    Kathirason 01
    Asokan 02
    Techispeaks 03
    Pro 100
    Ken 50

    Reply

  17. Tony, i’ve got something which is working where numeric value is at the end of the expression.

    Example :
    cast(reverse(cast(reverse(“Kathirason 02”) as signed)) as signed).

    You can put this mixed functions call in the ORDER BY, replacing “Kathirason 02” by the name of the column you want to sort.

    1) It reverses the expression => 20 Kathirason
    2) It casts it as signed value => 20
    3) It reverses it again to get the original value => 02
    4) It casts it as signed to make a numeric sort. => 2

    I hope it would be useful for somebody ;)

    Reply

  20. The best answer for this question is

    # tbl contains: 4a2d3,12a1b,1ac2 etc so on select * from tbl order by number_as_char * 1 asc, number_as_char asc expected output: 1ac2,4a2d3,12a1b#

    Reply

  22. I have varchar as

    cr491
    cr1003
    cr8352

    I have used

    SELECT * FROM `widows` ORDER BY
    cast(reverse(cast(reverse(substr(`ref`,2,4)) as signed)) as signed)

    but it orders as 1003,491,8352 instead of
    491,1003,8352 etc

    How can I achieve numeric order of the ‘suffix’ after the cr

    Reply

  23. Eureka

    SELECT * FROM `widows` ORDER BY lpad(rtrim(trim(leading ‘cr’ from ref)),4,’0′)

    After a lot of trial and error

    Reply