> 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]
