If you want the equality operator to be case-insensitive then your
column in the table should be declared "collate nocase". And it
doesn't matter whether you have index or not for this to work
(execution speed can differ though). But if you don't want that but
want like operator (which is case-insensitive by default) to use index
to optimize its execution then you should use "collate nocase" in the
index.
Pavel
On Sun, Jul 4, 2010 at 12:47 PM, Peng Yu <pengyu...@gmail.com> wrote:
> Hi,
>
> I see some examples on using "collate nocase" with "create index". I'm
> not sure what it means, as the following select statement still only
> return the line with 'aaa' but not the line with 'AAA'. Does the
> corresponding column in the table has to be "collate nocase" as well?
>
> create table test (id integer primary key, value text);
> create index value_index on test (value collate nocase);
> insert into test (value) values('bbb');
> insert into test (value) values('BBB');
> insert into test (value) values('aaa');
> insert into test (value) values('AAA');
> insert into test (value) values('ccc');
>
> select * from test where value='aaa';
>
>
> --
> Regards,
> Peng
> _______________________________________________
> sqlite-users mailing list
> sqlite-users@sqlite.org
> http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
>
_______________________________________________
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
