SQLite sorting question

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

Thank you,

Mur

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 (3A and 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.

UPD

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;