> From: "Chris Sebrell" <[EMAIL PROTECTED]>
>
> I'm trying to figure out if it's possible, using SQLite 2.8.13 (latest
version), to do
> case-insensitive ordering on a column.

Chris,

You can do what you want with an auxillary table and an index on that table.
In the example below your table would correspond to t1. T2 is the auxillary
table with the names stored in lower case. The index on t2 allows you to
order the tables by the case insensitive name quickly. The view t3 simply
makes it easier to access the joined tables. It is important to join the
tables with t2 first so that the index on the case insensitive name is used.

SQLite version 2.8.13
Enter ".help" for instructions
sqlite> .read case_test.sql

create table t1 (id integer primary key, name);

insert into t1 values(NULL, 'lkjhsdkjhfgkjh');
insert into t1 values(NULL, 'DSFGJKSD');
insert into t1 values(NULL, 'RTUSGDCJTQWVCVX');
insert into t1 values(NULL, 'tEBRF8743KJBD');
insert into t1 values(NULL, 'rtg76345jnbv9d8ubD');
insert into t1 values(NULL, 'Dsfdfvkn');

create table t2 as select id, lower(name) as ci_name from t1;
create index t2_ci_name on t2(ci_name asc);

create view t3 as select * from t2 join t1 using(id);

mode column
headers on
select id, name from t3 order by ci_name;
id          name
----------  ----------
6           Dsfdfvkn
2           DSFGJKSD
1           lkjhsdkjhf
5           rtg76345jn
3           RTUSGDCJTQ
4           tEBRF8743K

I hope this helps.

---------------------------------------------------------------------
To unsubscribe, e-mail: [EMAIL PROTECTED]
For additional commands, e-mail: [EMAIL PROTECTED]

Reply via email to