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, LLCYou 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:[EMAIL PROTECTED] P.S. A good JDO? O, Gasp!
smime.p7s
Description: S/MIME cryptographic signature
