Randy J. Ray wrote:
This may be a basic SQL question, but I can't find the answer in the
SQL-related documents on the site, so...
I have a field in a table that is typed as text, though it is 99% of the time
numerical. (It's used to track issue numbers of magazines, which for some
esoteric publications may be alphanumeric.)
I'd like to sort a query by this field, but when I do so "2" sorts after "10",
as is the age-old comp-sci problem with treating numbers as strings. Is there a
way, maybe with some variant of "SELECT field AS", to get SQLite to treat this
data as numerical for the sake of sorting?
Cast it as numeric in your ORDER BY clause:
create table dummy (a text);
insert into dummy values (2);
insert into dummy values (10);
select a from dummy order by a;
10
2
select a from dummy order by cast(a as numeric);
2
10
