On Friday, 17 March, 2017 16:13, Rob Willett <rob.sql...@robertwillett.com>
said:
> 3. We have looked through all our indexes and can see that every index
> has a COLLATE against it, even if the column is an integer. We have
> raised a support call with Navicat.
If you want a table field to be non-case-sensitive, the proper place to declare
that field as such is when you define the table.
create table Words (Word text unique collate nocase);
means that everywhere that you use Wowrds.Word is not case sensitive (but it is
case preserving). End of Story.
create table Words (Word text);
create unique index sqlite_autoindex_Words_1 on Words (Word collate nocase);
is however an entirely different thing. The data in Words.Word is case
sensitive, and the index is not case sensitive. Thus query will operate
differently depending on how you declared your table and index:
select * from Words where word = 'Apple';
if the former (nocase collation on the table) will (a) use the index and (b) be
equivalent to where lower(word) = lower('Apple') (that is, case insensitive)
in the case where you have declared a collate nocase index, the index will NOT
BE USED unless you specify COLLATE NOCASE in the where clause. By default the
search will be a table scan that is case sensitive.
_______________________________________________
sqlite-users mailing list
sqlite-users@mailinglists.sqlite.org
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
