Creating a case-insensitive database

When you create a database using locale-based collation, the collation=collation value TERRITORY_BASED uses the default collation strength for the locale, usually TERTIARY, which will consider character case significant in searches and comparisons.

To make the database use case-insensitive searches, specify an explicit strength lower than TERTIARY with the collation=collation attribute. The strength name is appended to TERRITORY_BASED with a colon to separate them.

For example, you could specify the following connection URL:

jdbc:derby:SwedishDB;create=true;territory=sv_SE;collation=TERRITORY_BASED:PRIMARY

With strength PRIMARY, the characters 'A' and 'a' will be considered equal, as well as 'à' ('a' with a grave accent). (This behavior is commonly the default with many other databases.) To make searches respect differences in accent, use strength SECONDARY.

The exact interpretation of the strength part of the attribute depends upon the locale.

For more information, see Creating a database with locale-based collation and the documentation of the territory=ll_CC and collation=collation attributes in the Derby Reference Manual.

Related concepts
Character-based collation in Derby
How collation works in Derby
Locale-based collation
Database connection URL attributes that control collation
Examples of case-sensitive and case-insensitive string sorting
Differences between LIKE and equal (=) comparisons
Related tasks
Creating a customized collator
Creating a database with locale-based collation