Mamta Satoor wrote:
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.
I think that the hard work being done on DERBY-1478 (by Mamta) sets the
foundation for any default collation order at the database level, such
as case-insensitive search. If anyone wants to scratch that itch they
should join in or follow the discussion on derby-dev for DERBY-1478.
https://issues.apache.org/jira/browse/DERBY-1478
Remember Derby is open source, if you need a feature please join the
development community and work on adding it.
http://wiki.apache.org/db-derby/#head-1a71e5af4a1c7bc0998cc44cc9f0b5cac9b5fea7
Thanks,
Dan.
On 3/12/07, *Jon Miller* <[EMAIL PROTECTED]
<mailto:[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
