Thanks for the idea. I hadn't thought about doing it that way.. Unfortunately, I am trying to keep my database size as small as possible, and the field I want to have indexed case-insensitively represents the bulk of the actual data. My current testing shows about 800MB for the database, +130MB after creating the index. Building a seperate table and storing a lower-case version of the field will add probably another 400MB or more.
Though I'm not using the source code to SQLite directory (I'm using the win32 DLL version), I suppose the source code could be modified without too much trouble to use stricmp() instead of strcmp().. would just have to make sure you do it in all the right places (and none of the wrong places.) Thanks again, Dennis! // CHRIS ----- Original Message ----- From: "Dennis Cote" <[EMAIL PROTECTED]> To: "Chris Sebrell" <[EMAIL PROTECTED]>; <[EMAIL PROTECTED]> Sent: Tuesday, April 27, 2004 1:40 PM 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]
