People use different tricks to do this. I Googled and find out some results each follow different tricks. Have a look at them:
- Alpha Numeric Sorting in MySQL
- Natural Sorting in MySQL
- Sorting of numeric values mixed with alphanumeric values
- mySQL natural sort
- Natural Sort in MySQL
Edit:
I have just added the code of each link for future visitors.
Alpha Numeric Sorting in MySQL
Given input
1A 1a 10A 9B 21C 1C 1D
Expected output
1A 1C 1D 1a 9B 10A 21C
Query
Bin Way
===================================
SELECT
tbl_column,
BIN(tbl_column) AS binray_not_needed_column
FROM db_table
ORDER BY binray_not_needed_column ASC , tbl_column ASC
-----------------------
Cast Way
===================================
SELECT
tbl_column,
CAST(tbl_column as SIGNED) AS casted_column
FROM db_table
ORDER BY casted_column ASC , tbl_column ASC
Natural Sorting in MySQL
Given input
Table: sorting_test -------------------------- ------------- | alphanumeric VARCHAR(75) | integer INT | -------------------------- ------------- | test1 | 1 | | test12 | 2 | | test13 | 3 | | test2 | 4 | | test3 | 5 | -------------------------- -------------
Expected Output
-------------------------- -------------
| alphanumeric VARCHAR(75) | integer INT |
-------------------------- -------------
| test1 | 1 |
| test2 | 4 |
| test3 | 5 |
| test12 | 2 |
| test13 | 3 |
-------------------------- -------------
Query
SELECT alphanumeric, integer
FROM sorting_test
ORDER BY LENGTH(alphanumeric), alphanumeric
Sorting of numeric values mixed with alphanumeric values
Given input
2a, 12, 5b, 5a, 10, 11, 1, 4b
Expected Output
1, 2a, 4b, 5a, 5b, 10, 11, 12
Query
SELECT version
FROM version_sorting
ORDER BY CAST(version AS UNSIGNED), version;
Hope this helps