I'm using SQLite DB in my Android Application.
There are numbers and strings in a column. Something like:
3, 1, 2, 6 / 6A, X, 3A, 10, 12, XY, 44, ZW
If I select values of this column and sort them, I get:
1, 10, 12, 2, 3, 3A, 44, 6 / 6A, X, XY, ZW
Is there any possibility in SQLite to sort these values so, that I would get:
1, 2, 3, 3A, 6 / 6A, 10, 12, 44, X, XY, ZW
You can sort the pure numbers and pure strings as desired simply by storing the numbers as numbers (you are currently storing everything as strings).
The hybrids (
6A) are trickier. One option is to extract the numeric prefix and store that in a sorting column (along with the other values unmodified):
original sort 1 1 2 2 6A 6 6 6 3A 3 X X XY XY
Then order by the sorting column followed by the original column:
SELECT * FROM MyTable ORDER BY sort, original
After many experiments I've got a proper query:
select _id, case when cast(_id as number) = 0 then _id when cast(_id as number) <>0 then cast(_id as number) end as sorting from lines order by sorting
The good thing is, that the casting operation returns for '6 / 6A' 6 back.
Don't know why, but if I use this query in my android application, I get _id column as number, so I must do the casting to text
select cast(_id as text) as _id, case when cast(_id as number) = 0 then _id when cast(_id as number) <>0 then cast(_id as number) end as sorting from lines order by sorting
Inspired by the solutions of Tima and Marcelo Cantos I found the following solution:
SELECT * FROM MyTable
ORDER BY CAST(original AS INTEGER), original;