----- 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. I have used this technique to query a database of a commercial application with a proprietary, inaccessible nocase collation sequence and over whose structure I have no control. Seems to work alright but I have no way of assessing what the cost penalty might be over the 'best way'. Besides, it was not for a 'life or death' application (albeit one that tracks lives and deaths). Thought I would throw it in as an option. 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. If some data did lie outside the intersection of the two collations, I suppose there would be some sorting errors if the indexes are left on the superset and the query uses the override with the subset collation. But then there could be data that lies outside the superset that would not be correctly sorted even with the 'best way'. Isn't that the conundrum caused by so many different character sets among the world's many languages? The more universal the collation sequence, the more expensive the index? Tom _______________________________________________ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
