How to use ORDER BY with proper handling of numbers?

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

SELECT name, model FROM vehicle ORDER BY model

The problem is, that for 'model' values like these: 'S 43', 'S开发者_运维技巧 111' the order will be:

S 111
S 43

because I suppose ORDER BY uses alphabetic order rules, right? So how to modify this query to get "numerical" order? In which 'S 43' would be before 'S 111'? Without changing or adding any data to this table.

Something like this:

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

Note, that it's not a good practice to sort by function result, because it produces dynamic unindexed result which can be very slow, especially on large datasets.

If the non-numeric portion's of constant length, you could

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

or, if the non-numeric portion's length varies, you could

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

You can take numeric part only (substring functions) and convert it into int (cast functions).

mySQL cast functions

mySQL string functions

I didn't test it myself but I suppose it should work.