I think, for simplicity, I would just add the additional columns required to store a duplicate field in upper or lower case for each search field required, then just index on those fields (columns.) The table update could have a trigger to automatically update the duplicate fields (columns.)
Fred > -----Original Message----- > From: Dennis Cote [mailto:[EMAIL PROTECTED] > Sent: Tuesday, April 27, 2004 12:41 PM > To: Chris Sebrell; [EMAIL PROTECTED] > Subject: Re: [sqlite] Case-Insensitive ordering? > > > > 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] > > --------------------------------------------------------------------- To unsubscribe, e-mail: [EMAIL PROTECTED] For additional commands, e-mail: [EMAIL PROTECTED]
