"Randy J. Ray" <[EMAIL PROTECTED]> 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? >
SQLite version 1 used to do that kind of sorting by default. But people hated it, so I took it out. Probably you can search through the CVS archives, pull out the old comparison function, and change it into a collating function. Register the collating function and then sort using it: SELECT ... ORDER BY catnum COLLATE string_as_number; -- D. Richard Hipp <[EMAIL PROTECTED]>
