3

i googled around a bit about case insensitive search in apache derby. all google results are very old (2007 the latest). i found that is impossible to search case insensitive without loosing the index ("LOWER" don't uses the index).

Is this still true? Or is there a way to get case insensitive search on indexed varchar/text columns?

thx in advance

dermoritz
  • 12,519
  • 25
  • 97
  • 185

1 Answers1

3

Have a look at collation:

You could use TERRITORY_BASED:SECONDARY` when creating the connector, this was the only way I was able to achieve this:

TERRITORY_BASED:SECONDARY: Territory based with collation strength SECONDARY.
SECONDARY typically means that differences in base letters or accents are considered significant, whereas differences in case are not considered significant.

Example:

jdbc:derby:MexicanDB;create=true;collation=TERRITORY_BASED:SECONDARY

Apparently it is not possible in Derby to create an index over a function: https://issues.apache.org/jira/browse/DERBY-455

Another possibility is to store the same value in a lower case column and search in that.

oers
  • 18,436
  • 13
  • 66
  • 75
  • 1
    thx i also found this http://stackoverflow.com/questions/6084000/case-insensitive-search-in-java-db-derby – dermoritz Feb 02 '12 at 10:53
  • great, an answer there mentions: http://blogs.oracle.com/kah/entry/derby_10_5_preview_generated to use generated columns, which would be useful for my last proposal – oers Feb 02 '12 at 11:02
  • but your suggestion is less invasive - at the moment the better choice for me. – dermoritz Feb 02 '12 at 11:22