Q: SQLite sort varchar that contains letter and number I have a table where I have field traffic_num(varchar) that contains this data
A100
A586
A4594
A125
A2
A492

now I want to sort that data ascending order. It would be really easy if traffic_num contains only number (without letter A), then I could cast varchar to integer CAST(traffic_num as SIGNED INTEGER) ASC. But what to do in this situation?

A: You can use substr to first order by the first alphabet character and then by the number following it.
select * from tablename
order by substr(traffic_num,1,1), cast(substr(traffic_num,2) as signed integer)


A: A simple way to do this is by sorting by the length and then the value:
order by length(traffic_num), traffic_num

This works when the values are integers.
