MySQL order by string with numbers

If there can be multiple characters at the beginning of the string, for example like 'M10', 'MTR10', 'ABCD50', 'JL8', etc..., you basically have to get the substring of the name from the first position of a number.

Unfortunately MySQL does not support that kind of REGEXP operation (only a boolean value is returned, not the actual match).

You can use this solution to emulate it:

SELECT   name
FROM     tbl
ORDER BY CASE WHEN ASCII(SUBSTRING(name,1)) BETWEEN 48 AND 57 THEN
                   CAST(name AS UNSIGNED)
              WHEN ASCII(SUBSTRING(name,2)) BETWEEN 48 AND 57 THEN
                   SUBSTRING(name,1,1)
              WHEN ASCII(SUBSTRING(name,3)) BETWEEN 48 AND 57 THEN
                   SUBSTRING(name,1,2)
              WHEN ASCII(SUBSTRING(name,4)) BETWEEN 48 AND 57 THEN
                   SUBSTRING(name,1,3)
              WHEN ASCII(SUBSTRING(name,5)) BETWEEN 48 AND 57 THEN
                   SUBSTRING(name,1,4)
              WHEN ASCII(SUBSTRING(name,6)) BETWEEN 48 AND 57 THEN
                   SUBSTRING(name,1,5)
              WHEN ASCII(SUBSTRING(name,7)) BETWEEN 48 AND 57 THEN
                   SUBSTRING(name,1,6)
              WHEN ASCII(SUBSTRING(name,8)) BETWEEN 48 AND 57 THEN
                   SUBSTRING(name,1,7)
         END,
         CASE WHEN ASCII(SUBSTRING(name,1)) BETWEEN 48 AND 57 THEN
                   CAST(SUBSTRING(name,1) AS UNSIGNED)
              WHEN ASCII(SUBSTRING(name,2)) BETWEEN 48 AND 57 THEN
                   CAST(SUBSTRING(name,2) AS UNSIGNED)
              WHEN ASCII(SUBSTRING(name,3)) BETWEEN 48 AND 57 THEN
                   CAST(SUBSTRING(name,3) AS UNSIGNED)
              WHEN ASCII(SUBSTRING(name,4)) BETWEEN 48 AND 57 THEN
                   CAST(SUBSTRING(name,4) AS UNSIGNED)
              WHEN ASCII(SUBSTRING(name,5)) BETWEEN 48 AND 57 THEN
                   CAST(SUBSTRING(name,5) AS UNSIGNED)
              WHEN ASCII(SUBSTRING(name,6)) BETWEEN 48 AND 57 THEN
                   CAST(SUBSTRING(name,6) AS UNSIGNED)
              WHEN ASCII(SUBSTRING(name,7)) BETWEEN 48 AND 57 THEN
                   CAST(SUBSTRING(name,7) AS UNSIGNED)
              WHEN ASCII(SUBSTRING(name,8)) BETWEEN 48 AND 57 THEN
                   CAST(SUBSTRING(name,8) AS UNSIGNED)
         END

This will order by the character part of the string first, then the extracted number part of the string as long as there are <=7 characters at the beginning of the string. If you need more, you can just chain additional WHENs to the CASE statement.

You could use SUBSTR and CAST AS UNSIGNED/SIGNED within ORDER BY:

SELECT * FROM table_name ORDER BY
    SUBSTR(col_name FROM 1 FOR 1),
    CAST(SUBSTR(col_name FROM 2) AS UNSIGNED)