Re: [sqlite] Alpha numeric collation

[John Elrick]

Simon Davies wrote:
> If your data is in an integer column then an order by on the data
> column gives what you ask for:
>   
SNIP
> sqlite> select data from tst2 order by case cast( data as integer
> )=data when 1 then cast( data as integer ) else data end;
> 1
> 4
> 9
> 10
> 51
> a

Thanks very much for your assistance Simon.  The first case may indeed 
work, the field is currently varchar but there is nothing preventing me 
from making it integer, and the second case gives me some insights I 
hadn't considered.

Appreciate your time,


John Elrick
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

[sqlite] Alpha numeric collation

[John Elrick]

I understand this problem can be solved with a custom collation, 
however, if at all possible I'd prefer to not reinvent the wheel.  I was 
curious if there were any pre-existing solutions my Google searches and 
reading of the documentation failed to detect.

The problem: Is there any existing collation solution that respects 
numeric values intermixed with alpha?  As an example, if we have the 
following rows:

1
a
10
4
51
9

traditional alpha-numeric sorting would sort them as follows:

1
10
4
51
9
a

What the customer desires is for the sorting to treat strictly numeric 
values as though they were indeed numerics:

1
4
9
10
51
a

Thanks for any feedback, including "nope, you have to roll your own".


John Elrick
Fenestra Technologies
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users