Creating a database with territory-based collation

By default, Derby uses Unicode codepoint collation. However, you can specify territory-based collation when you create the database.

You can use the collation and territory attributes to specify territory-based collation. This type of collation applies only to user-defined tables. The system tables use the Unicode codepoint collation.

Restriction: The collation attribute can be specified only when you create a database. You cannot specify this attribute on an existing database or when you upgrade a database.

To create a database with territory-based collation:

Specify the language and country codes for the territory attribute, and the TERRITORY_BASED value for the collation attribute when you create the database.

For example:

jdbc:derby:MexicanDB;create=true;territory=es_MX;collation=TERRITORY_BASED

Creating a case-insensitive database

The 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 with the collation attribute. The strength name is appended to TERRITORY_BASED with a colon to separate them.

For example:

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 Character-based collation in Derby and the documentation of the collation attribute in the Derby Reference Manual.