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 |

| 11      |
| 1       |
| 10      |
| 12      |

| 5b      |
| 2a      |
| 4b      |
| 5a      |
+---------+

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       |

| 10      |
| 2a      |
| 4b      |
| 5a      |
| 5b      |
| 11      |

8 rows in set, 4 warnings (0.00 sec)
| 12      |
+---------+