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; ```

Top