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]

Reply via email to