Hi Jon, Currently, you can't configure Derby to do case insensitive searches on CHAR/VARCHAR. Using LOWER() function is a possible solution but as you suspected, this will have a performance hit because Derby does not support functional indexes. So, eventhough you might have an index on the column, the LOWER() function on that column will not use the index on column. I think DERBY-455 is the Jira entry for functional index support in Derby.
Mamta ps There is effort going on to support language based ordering as part of DERBY-1478 but that probably won't help you with case insensitive search. On 3/12/07, Jon Miller <[EMAIL PROTECTED]> wrote:
Hi all, I'm wondering if anyone knows if it's possible to configure Derby so that searches on CHAR/VARCHAR fields are case insensitive by default? I'm guessing that if the answer is no, the solution is to just do a LOWER() or similar on the field and compare it that way. I'm guessing if you do that though, it won't use the index if the field is indexed? Jon
