CODEWITHSUNDEEP
sqlsqlitesql-order-by
David
David

Reputation: 3105

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?

Upvotes: 0

Views: 492

Answers (2)

Gordon Linoff
Gordon Linoff

Reputation: 1270463

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.

Upvotes: 1

Vamsi Prabhala
Vamsi Prabhala

Reputation: 49260

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)

Upvotes: 1

Related Questions