CODEWITHSUNDEEP
mysql
Virendra Pratap Singh
Virendra Pratap Singh

Reputation: 25

Sorting on varchar field as integer in mysql

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.

Upvotes: 0

Views: 519

Answers (1)

lurker
lurker

Reputation: 58224

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.

Upvotes: 1

Related Questions