Archives

Sorting of numeric values mixed with alphanumeric values

13 Comments

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)

13 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

  3. 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

  5. Pingback: MySQL ORDER BY Send first to last - Technology

  8. 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)

  11. hi, i need to sort :

    A.1
    B.1.1
    B.1.2
    C.1
    D.1.1
    D.1.2
    D.1.3
    D.2.1.1
    D.2.1.2
    D.3
    D.4
    ….
    D.9
    D.10
    D.11
    D.12

    Thanks for help

  12. SELECT MyField
    FROM MyTable
    order by
    IF( MyField REGEXP ‘^-?[0-9]+$’ = 0,
    9999999999 ,
    CAST(MyField AS DECIMAL)
    ), MyField

  13. SELECT Square
    FROM Table1
    ORDER BY
    CASE WHEN Square REGEXP ‘^[A-Z]{2}’
    THEN 1
    ELSE 0
    END ASC,
    CASE WHEN Square REGEXP ‘^[A-Z]{2}’
    THEN LEFT(Square, 2)
    ELSE LEFT(Square, 1)
    END ASC,
    CASE WHEN Square REGEXP ‘^[A-Z]{2}’
    THEN CAST(RIGHT(Square, LENGTH(Square) – 2) AS SIGNED)
    ELSE CAST(RIGHT(Square, LENGTH(Square) – 1) AS SIGNED)
    END ASC

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.