----- 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

Reply via email to