On Tue, Mar 30, 2010 at 10:59:49PM -0400, Tom Holden scratched on the wall: > > ----- Original Message ----- From: "Jay A. Kreibich" <j...@kreibi.ch> > To: "General Discussion of SQLite Database" <sqlite-users@sqlite.org> > Sent: Tuesday, March 30, 2010 9:26 PM > Subject: Re: [sqlite] Case-sensitivity, performance and LIKE > > >> On Tue, Mar 30, 2010 at 08:41:09PM -0400, Tom Holden scratched on the >> wall: >>> >>> ----- Original Message ----- From: "Simon Slavin" >>> <slav...@bigfraud.org> >> >>> > columnName TEXT COLLATE NOCASE >>> > >>> > then all sorting and SELECT queries on it will ignore case. >>> >>> You don't even have to change the defined collation as you can impose the >>> NOCASE collation in the SELECT statement as: >>> >>> SELECT * from Customers WHERE LastName = 'Shaw' COLLATE NOCASE AND >>> FirstName >>> = 'Gioia' COLLATE NOCASE;
>> If either/both of these columns has/have indexes, it is best to change >> it in the table definition. Otherwise you also need to define it in the >> index definition, as well as everywhere you expect to use the index. >> This is all automatic if everything is built with the collation in >> the table definition. > Maybe it works OK because the custom collation is a superset of the > integral NOCASE collation and my data does not lie outside the latter. Sorry... I didn't mean to imply this was wrong. Using a specific collation in a query is perfectly OK, and should always result in the correct answer. The issue is that a query will only use an index if the query collation and the index collation match. Given the OP's performance motivations, it seemed important. Hence, for general situations where performance is a concern, it is best to just declare the whole column with whatever collation you want to use. Otherwise, it is all too easy to miss something somewhere and end up with a much slower query. For one-off specific instances when you want to use a specific collation (or, as in your situation when you cannot re-define the table) it is perfectly valid to simply add the collation to the query. However, that will result in a full table-scan, even if an index is otherwise available. -j -- Jay A. Kreibich < J A Y @ K R E I B I.C H > "Our opponent is an alien starship packed with atomic bombs. We have a protractor." "I'll go home and see if I can scrounge up a ruler and a piece of string." --from Anathem by Neal Stephenson _______________________________________________ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
