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* <jemiller@uchicago.edu
> <mailto:jemiller@uchicago.edu>> 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
>
>
|