I also believe there is no way to get derby to automatically do case insensitive searching.
Someday I hope that someone builds indexes on a function in derby. Building the index should not be too hard, I think the more difficult/interesting work is to get the optimizer to choose the index, and getting the execution engine to use the index. Case insensitive search is one example of a feature which could be addressed by functional indexes. /mikem Jack Klebanoff wrote: > Jonathan Eric Miller wrote: > >> I'm guessing that the answer to this is that you can't. So, I'm >> guessing you just use the LOWER() function like the following. >> >> SELECT * FROM Person WHERE LOWER(firstName) = 'jonathan'; >> >> However, if you do this, then it won't use an index if you have an >> index on the column that you are filtering on, correct? >> >> Jon >> >> ----- Original Message ----- From: "Jonathan Eric Miller" >> <[EMAIL PROTECTED]> >> To: "Derby User List" <[email protected]> >> Sent: Monday, January 24, 2005 9:19 AM >> Subject: Re: How to configure Derby to be case-insensitive? >> >> >>> Does anyone know the answer to this? >>> >>> Jon >>> >>> ----- Original Message ----- From: "Jonathan Eric Miller" >>> <[EMAIL PROTECTED]> >>> To: "Derby User List" <[email protected]> >>> Sent: Thursday, January 20, 2005 3:36 PM >>> Subject: How to configure Derby to be case-insensitive? >>> >>> >>>> Does anyone know if it's possible to configure Derby so that >>>> searches are case-insensitive? >>>> >>>> Jon >>>> >>> >> > I do not think that you can configure Derby so that searches are > case-insensitive. > > I believe that you are also right that using the LOWER function in the > WHERE clause will cause a table scan to be used. The index is not used > because the Btree stores keys in upper and lower case so the matching > keys are scattered around the Btree. The optimizer might be able to use > the index by anding in the expression ((firstName >= 'j' AND firstName < > 'k') OR (firstName >= 'J' AND firstName < 'K'), but I do not think that > it does this. A project for someone who is interested in working on the > optimizer. > > A way of making searches case insensitive is for the application to > convert all data to a canonical case (upper or lower). That is what > Derby does internally to handle table and column names: it converts all > unquoted table and column names to upper case. > > Jack >
