"Evans, Mark (Tandem)" <[EMAIL PROTECTED]> wrote:
> ....snip...
>
> >
> > LIKE operators cannot use indices unless the index is case
> > insensitive. Use GLOB for case sensitive fields.
> >
>
> Richard - i'm not sure i understand "unless the index is case insensitive."
> How does that relate to:
>
> sqlite> create table t (a varchar(10) primary key, b, c);
> sqlite> pragma CASE_SENSITIVE_LIKE=OFF;
> sqlite> explain query plan select * from t where a like 'a%';
> 0|0|TABLE t
> sqlite> pragma CASE_SENSITIVE_LIKE=ON;
> sqlite> explain query plan select * from t where a like 'a%';
> 0|0|TABLE t WITH INDEX sqlite_autoindex_t_1
> sqlite>
>
> Dumb question: Is CASE_SENSITIVE_LIKE a different concept
> from "case sensitive index"?
>
Yes it is. By default ('A' LIKE 'a') is true. But if you enable
case-sensitive like then ('A' LIKE 'a') is false.
The case sensitivity of your LIKE operator must match the
case sensitivity of your indices in order for the index
to be usable as an optimization.
--
D. Richard Hipp <[EMAIL PROTECTED]>
_______________________________________________
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
