How to use ORDER BY with the correct processing of numbers?

Question

I want to get data from mysql table sorted by one of varchar columns. So let's say I have a query like this:

SELECT name, model FROM vehicle ORDER BY model 

The problem is that for "model" values ​​like these: 43 ', 111', the order will be:

 S 111 S 43 

because I assume that ORDER BY uses alphabetical rules, right? So, how to change this query to get a "numerical" order? In which 43 'would be up to 111'? Without changing or adding any data to this table.

Answer 1

Something like that:

 SELECT name, model FROM vehicle ORDER BY CAST(TRIM(LEADING ' FROM model) AS INTEGER) 

Note that this is not a good practice for sorting by function result, because it creates a dynamic non-indexed result, which can be very slow, especially on large datasets.

Answer 2

You can only accept the numeric part (substring functions) and convert it to int (translation functions).

mySQL cast functions

mySQL string functions

I have not tested it myself, but I suppose it should work.

Answer 3

If the non-numeric part is of constant length, you can

 ORDER BY substring(model, <length of non-numeric portion>) 

or, if the length of the non-numeric part changes, you can

 ORDER BY substring(model, 1 + LOCATE(' ', model))