MySQL ORDER BY Sorting alphanumeric on correct order

source: (user9385340)(2022), https://stackoverflow.com/questions/8557172/mysql-order-by-sorting-alphanumeric-correctly

example:

SELECT `alphanumericCol` FROM `tableName` ORDER BY 
  SUBSTR(`alphanumericCol` FROM 1 FOR 1), 
  LPAD(lower(`alphanumericCol`), 10,0) ASC

output:

1
2
11
21
100
101
102
104
S-104A
S-105
S-107
S-111