Sorting on varchar field as integer in mysql

Question

I have a table which has three fields: "Id, Name, Sequence".
In the "Sequence" column there are the following entries: 1, 2, 3, 4, 2a, 5, 2b, 2c, 3, 4a (Row wise).

Now I want to a mysql query which can sort these values like:

1, 2, 2a, 2b, 2c, 3, 4, 4a, 5.

I have tried following query

SELECT * FROM table_name ORDER BY CAST(sequence AS UNSIGNED) ASC.
But it's not working.

you don't need anything but SELECT * FROM table_name ORDER BY sequence asc — senape
– senape, Commented Feb 20, 2018 at 17:07
@senape that will put 10 before 2 since the field is varchar not numeric. — lurker
– lurker, Commented Feb 20, 2018 at 17:08
@lurker you're right, I didn't think about it. actually, I tested with cast(sequence as unsigned) and it worked as expected. — senape
– senape, Commented Feb 20, 2018 at 17:12
@senape cast(sequence as unsigned) by itself doesn't work, either. It doesn't subsort 2, 2a, 2b for example. If your list has 2b, 2, 2a, then it leaves that order. — lurker
– lurker, Commented Feb 20, 2018 at 17:13
@senape, no it doesn't. See this example on SQL Fiddle. 3b comes before 3, but it shouldn't. — lurker
– lurker, Commented Feb 20, 2018 at 17:15

lurker · Accepted Answer · 2018-02-20 17:14:56Z

1

You want to order by the integer first, then order by the substring after the number by just ordering by the whole string as the sub-sort:

SELECT * FROM table_name ORDER BY CAST(sequence AS UNSIGNED), sequence;

Here's an SQL Fiddle with an example. I left off the ASC since that's the default.

edited Feb 20, 2018 at 17:14

answered Feb 20, 2018 at 17:08

lurker

58.5k9 gold badges74 silver badges108 bronze badges

Sign up to request clarification or add additional context in comments.

1 Comment

lurker Over a year ago

@VirendraPratapSingh that's great! If you could be so kind as to accept this answer by clicking the check mark next to it, I would appreciate it.

Collectives™ on Stack Overflow

Sorting on varchar field as integer in mysql

1 Answer 1

1 Comment

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

1 Comment

Your Answer

Sign up or log in

Post as a guest

Related