# collation=collation attribute

The collation=collation attribute is an optional attribute that specifies whether collation is based on the locale specified for the database or on Unicode codepoint collation.

The valid values for the collation=collation attribute are:

UCS_BASIC
  Unicode codepoint collation. This value is the default.
TERRITORY_BASED
  Based on the language specified with the territory=ll_CC attribute. The default collation strength for the locale is used. The default for Derby is commonly TERTIARY, in which character case is significant in searches and comparisons.
TERRITORY_BASED:PRIMARY
  Locale based with collation strength PRIMARY. Specify this value to make Derby behave similarly to many other databases, for which PRIMARY is commonly the default. PRIMARY typically means that only differences in base letters are considered significant, whereas differences in accents or case are not considered significant.
TERRITORY_BASED:SECONDARY
  Locale 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.
TERRITORY_BASED:TERTIARY
  Locale based with collation strength TERTIARY. TERTIARY typically means that differences in base letters, accents, or case are all considered significant.
TERRITORY_BASED:IDENTICAL
  Locale based with collation strength IDENTICAL. IDENTICAL means that all differences are considered significant.

Restriction: The collation=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.

If you specify the collation=collation attribute with the value TERRITORY_BASED, or one of its variants with a specific collation strength, the collation is based on the language and country codes that you specify with the territory=ll_CC attribute.

If you do not specify the territory=ll_CC attribute when you create the database, Derby uses the java.util.Locale.getDefault method to determine the current value of the default locale for this instance of the Java Virtual Machine (JVM).

Note: The collation=collation attribute applies only to user-defined tables. The system tables use the Unicode codepoint collation.

For information on how Derby handles collation, see "Creating a database with locale-based collation" and "Character-based collation in Derby" in the Derby Developer's Guide.

## Example

The following example shows the URL to create the MexicanDB database. The territory=ll_CC attribute specifies Spanish for the language code and Mexico for the country code. The collation=collation attribute specifies that the collation for the database is locale based.

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

Parent topic: Setting attributes for the database connection URL