Sorting of numeric values mixed with alphanumeric values

Assume that you have a list of numeric values mixed with numeric
values appended with characters – like 2a, 12, 5b, 5a, 10, 11, 1, 4b.

Sorting on a list like this would usually end up so:

mysql> SELECT version
-> FROM version_sorting
-> ORDER BY version;
+---------+
| version |
+---------+
| 1       |
| 10      |
| 11      |
| 12      |
| 2a      |
| 4b      |
| 5a      |
| 5b      |
+---------+
8 rows in set (0.03 sec)

To sort the list based on the numeric values, simply do this:

mysql> SELECT version
-> FROM version_sorting
-> ORDER BY CAST(version AS UNSIGNED), version;
+---------+
| version |
+---------+
| 1       |
| 2a      |
| 4b      |
| 5a      |
| 5b      |
| 10      |
| 11      |
| 12      |
+---------+

8 rows in set, 4 warnings (0.00 sec)

10 thoughts on “Sorting of numeric values mixed with alphanumeric values”

  1. That is really cool. Do you know of any way to do it if the reverse is true. So here is my list:
    BG1
    BG11
    BG12
    BG13
    BG2
    BG3
    BG4

    Do you know how I can order those correctly?
    Thanks
    Mike

  2. Works 😀

    i have a colum with:
    1-200D1
    1-200A1
    2-200D1
    2-200A1
    4-200A1
    4-200D1
    3-200A1
    3-200D1

    it’s possible to get order like:
    1-200D1
    1-200A1
    2-200D1
    2-200A1
    3-200D1
    3-200A1
    4-200D1
    4-200A1

    Best Regards,
    Marco

  3. This seems to be the default sorting anyway:

    mysql> select * from t order by txt;
    +----+-------+
    | id | txt   |
    +----+-------+
    |  1 | 1     |
    |  2 | 1 a   |
    |  3 | 2     |
    |  4 | 2a    |
    |  5 | 5     |
    |  6 | apple |
    |  7 | ball  |
    |  8 | xerox |
    +----+-------+
    8 rows in set (0.00 sec)

Leave a Reply

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