Alpha Numeric Sorting in MySQL

If it is required to sort columns like this

1A 1a 10A 9B 21C 1C 1D

And Our desired Result is

1A 1C 1D 1a 9B 10A 21C

We can use casting, but casting will not sort 1A, 1B,1C properly. To overcome this issue we are first converting this column to binary and sorting the binary combined with tbl_column.

hehe,,, after doing some headbanging just did it with casting too... and using same logic... damn why I didnt thought of that.

1. BIN Way
2. ===================================
3. SELECT
4. tbl_column,
5. BIN(tbl_column) AS binray_not_needed_column
6. FROM db_table
7. ORDER BY binray_not_needed_column ASC , tbl_column ASC
8.  
9. -----------------------
10.  
11. Cast Way
12. ===================================
13. SELECT
14. tbl_column,
15. CAST(tbl_column as SIGNED) AS casted_column
16. FROM db_table
17. ORDER BY casted_column ASC , tbl_column ASC