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.I do not think that you can configure Derby so that searches are case-insensitive.
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 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
