db-derby-user mailing list archives

Site index · List index
Message view « Date » · « Thread »
Top « Date » · « Thread »
From Craig L Russell <Craig.Russ...@Sun.COM>
Subject Re: case insensitive searches
Date Thu, 10 Aug 2006 01:33:02 GMT

On Aug 9, 2006, at 4:27 PM, Farukh S. Najmi wrote:

> Stephen Caine wrote:
>
>> Terry,
>>
>>> Is it possible to set Derby to do case-insensitive searches? The  
>>> default seems to be case-sensitive. This would be fine as a  
>>> global setting that never needs to change.
>>>
>>
>> Are you using 'starts with', 'contains' or 'equals'?  These  
>> operators tend to be case sensitive.  Can you use 'like'?  This is  
>> case insensitive.
>>
>> Stephen Caine
>> Soft Breeze Systems, LLC
>
> You can also use UPPER function in your query predicate as in:
>
> ... AND UPPER(name) = 'CAINE' ....

You should be aware that using UPPER(column) or LOWER(column)  
disallows the use of any indexes, so this should be a secondary  
qualifier for a query, not a primary qualifier.

That is, if you want to have a case-insensitive search for name,  
where that's the only search qualifier, you are going to have the  
performance of a table scan, whereas if you are looking for a  
customer id within some range (customer id is indexed) and  
secondarily a case-insensitive search for name, that is ok.

If you want an efficient case-insensitive query, you should consider  
adding another column to the table that contains the UPPER or LOWER  
translation of a column. Or add a soundex [1] column for better  
searches.

Craig

[1] http://en.wikipedia.org/wiki/Soundex
>
> -- 
> Regards,
> Farrukh
>
> Web: http://www.wellfleetsoftware.com
> Blog: http://farrukhnajmi.blogspot.com
>

Craig Russell
Architect, Sun Java Enterprise System http://java.sun.com/products/jdo
408 276-5638 mailto:Craig.Russell@sun.com
P.S. A good JDO? O, Gasp!


Mime
View raw message