Search code examples
derbycase-insensitive

Apache Derby - case (in)sensitivity

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

Solution

  • 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.