SQLite – order by numbers inside string

its my first post here, so bear with me.

I’m trying to order a query by numbers in a specific row that contain letters, using SQLite.

Example: “Winter 1993”.

I want to be able to sort by the numbers only, without altering the table structure.

My query:

select Col from table order by Col*1, Col Asc

The query sorts by letters first and then by numbers, I just want it sorted by numbers.

Anyone has any idea how to do this?

Answer

So it would be {Season} {Year}

If the numbers are consistently located after the first space in the string, we can use string functions to extract them as follows:

select col
from mytable
order by substr(col, instr(col, ' ') + 1) + 0, col