>> If you want the equality operator to be case-insensitive then your
>> column in the table should be declared "collate nocase".
> 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'?
In this case if you don't mention collate in index creation statement
it will be "collate nocase" automatically.
>> 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?
For this case no, you don't. Just beware that this index won't be used
for simple equality operator because it uses collation declared in the
table by default.
Pavel
On Sun, Jul 4, 2010 at 1:24 PM, Peng Yu <pengyu...@gmail.com> wrote:
> 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
>
_______________________________________________
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
