db-derby-user mailing list archives

Site index · List index
Message view « Date » · « Thread »
Top « Date » · « Thread »
From Daniel John Debrunner <...@apache.org>
Subject Re: Case insensitive indexed search possible?
Date Fri, 02 Nov 2007 16:03:05 GMT
Bernt M. Johnsen wrote:
>>>>>>>>>>>>> Rick Hillegas wrote (2007-11-02 05:21:09):
>> Hi Jon,
>>
>> The situation with case-insensitive searching did not change in the 
>> latest 10.3 release and no one has volunteered to address this issue in 
>> the next feature release, 10.4. If you want to avoid the full table 
>> scan, you can store the text in  two columns:  mixed case in one column, 
>> and normalized to lowercase in the second column.
> 
> Note that in standard SQL, there is no feature that allows for case
> insensitive searching, so Rick's proposal with a canonical
> representation (e.g. lowercase) of the data is the only portable way
> of doing this.

SQL does allow implementation specified collations and thus provides the 
framework to perform case-insensitive searching, though specifying (ie. 
DDL or create database) how will be database specific.

With the language based ordering added to 10.3 Derby has the basic 
blocks to implement case-insensitive searching. At the database level 
(applies to all user character strings) would be the easiest, then at 
the schema level (all data within that schema), then at the data type level.

The database level would require adding support for a new collation, and 
then ensuring that collation leads to the correct java.text.Collator 
being set up. I think it might be as easy as using the current code and 
setting the strength of the current collator to be something other than 
primary(?).

Dan.

Mime
View raw message