mysql sorting string according to number first

Question

I don't know is it possible or not I have subject code stings like this

Math 451
Math 451a
Math 452
Math 452a
Math 452b

I want query that would sort these string like this

Math 451
Math 452
Math 451a
Math 452a
Math 452b

I tried

CAST(SUBSTR(subjects.code,INSTR(subjects.code, " ") + 1) AS UNSIGNED)

but not working as expected thank you for your any help and suggestions

Lukasz Szozda · Accepted Answer · 2017-08-13 16:57:43Z

1

You could first sort by length of string and then by string itself:

SELECT code
FROM subjects
ORDER BY LENGTH(code), code;

answered Aug 13, 2017 at 16:57

Lukasz Szozda

181k26 gold badges278 silver badges326 bronze badges

Sign up to request clarification or add additional context in comments.

1 Answer 1