Quantcast

Sorting a text field as if it were integer/float

Previous Topic Next Topic
 
classic Classic list List threaded Threaded
4 messages Options
Reply | Threaded
Open this post in threaded view
|  
Report Content as Inappropriate

Sorting a text field as if it were integer/float

Randy J. Ray
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?

Randy
--
"""""""""""""""""""""""""""""""""""""""""""""""""""""""""""""""""""""""""""""""
Randy J. Ray        Campbell, CA    http://www.rjray.org   [hidden email]

Silicon Valley Scale Modelers: http://www.svsm.org
Reply | Threaded
Open this post in threaded view
|  
Report Content as Inappropriate

Re: Sorting a text field as if it were integer/float

Eric Bohlman
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

Reply | Threaded
Open this post in threaded view
|  
Report Content as Inappropriate

Re: Sorting a text field as if it were integer/float

D. Richard Hipp
In reply to this post by Randy J. Ray
"Randy J. Ray" <[hidden email]> 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   <[hidden email]>

Reply | Threaded
Open this post in threaded view
|  
Report Content as Inappropriate

Re: Sorting a text field as if it were integer/float

ashutosh
This post has NOT been accepted by the mailing list yet.
I am also facing this type of issue in android, In android it uses sqlite to save data.
Now I am storing alphanumeric values in the column data which type is string.
Now using ordber by or ordber by (data as integer) is not solving my problem and also using other technique as well.
Below is the demo data, As the pattern is not fixed because song name is stored in db.
It may be like, 1song.mp3, 2song.mp3,I see you.mp3, doctor.mp3, fade.mp3, 8song.mp3, song12dd.mp3, 9song.mp3,10song.mp3,11song.mp3,song12dd.mp3.

Expected result is
1song.mp3
2song.mp3
8song.mp3
9song.mp3
10song.mp3
11song.mp3
song.mp3
song12dd.mp3
doctor.mp3
fade.mp3
I see you.mp3

So can you please help me out to solve above issue.
Loading...