Adam Esterle Adam Esterle - 1 month ago 15
MySQL Question

MySQL order by string with numbers

I have strings such as

M1 M3 M4 M14 M30 M40
etc (really any int 2-3 digits after a letter)
When I do " ORDER BY name " this returns:

M1, M14, M3, M30, M4, M40


When I want:

M1, M3, M4, M14, M30, M40

Its treating the whole thing as a string but I want to treat it as string + int

Any ideas?

Answer

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)