For example, I have this data in column number: M1, M2, ... M9, M10, U1, U2. In the MySQL query I have: ORDER BY number ASC, but the order display as: M1, M10, M2 ... U1, U2. What can I do to make the order as follows: M1, M2, ... M9, M10, U1, U2? Is this possible?

CodePudding user response：

If you strip the first character, then cast the result to its integer value, you can do this:

...ORDER BY SUBSTRING(mycolumn, 1, 1),
            SUBSTRING(mycolumn, 2)   0

Updated to show sorting by letter, then by numeric value following the letter.