On Sun, Jul 4, 2010 at 12:10 PM, Pavel Ivanov <paiva...@gmail.com> wrote:
> 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
So if I care the speed, then I should declare the index as collate
nocase besides creating the table with the column as 'collate nocase'?
> 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.
In this case, I don't need to specify the column to be collate nocase
when I create the table?
> 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
>
--
Regards,
Peng
_______________________________________________
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
