Character-based collation in Derby

A character set is a set of symbols and encodings. Character data types are represented as Unicode 2.0 sequences in Derby.

How collation works in Derby

Derby supports a wide range of character sets and encodes all of the character sets by using the Unicode support provided by the java.lang.Character class in the Java Virtual Machine (JVM) in which the Derby database runs. See the Java API documentation for the java.lang.Character class for the exact level of Unicode Standard that is supported.

A collation is a set of rules for comparing characters in a character set. In Derby the collation rules affect comparisons of the CHAR and VARCHAR data types. Collation rules also affect how the LIKE Boolean operator processes the CHAR, VARCHAR, CLOB, and LONG VARCHAR data types.

The default Derby collation rule is based on the binary Unicode values of the characters. So a character is greater than (<), equal to (=), or less than (>) another character based on the numeric comparison of the Unicode values. This rule allows for very efficient comparisons of character strings.

Note: When LIKE comparisons are used, Derby compares one character at a time for non-metacharacters. This is different than the way Derby processes = comparisons. The comparisons with the = operator compare the entire character string on left side of the = operator with the entire character string on the right side of the = operator. See the Differences between LIKE and equal (=) comparisons section below.

Territory-based collation

Derby also supports the ability to define collation rules that are appropriate to a territory, and is referred to as territory-based collation. Derby supports the territories that Java supports.

You can specifically set the territory of a database when you create the database. If you do not specify a territory, Derby uses the default territory of the JVM in which the database is created. Each JVM can support many territories that are independent from the default territory for the JVM. Collation support for these additional territories is provided through the java.text.RuleBasedCollator class and the set of rules for these territories. Refer to the JVM specification for details of how these rules are used to provide territory specific collation. Derby currently supports only running those rules that can be loaded dynamically from the running JVM based on the territory attribute. Overrides to these rules by the user are not supported.

The territory-based collation in Derby affects how the CHAR and VARCHAR data types are compared. Specifying territory-based collation also impacts how the LIKE Boolean operator processes CHAR, VARCHAR, CLOB, and LONG VARCHAR data.

Territory-based collation does add extra processing overhead to all character-based comparison operations.

Database attributes that control collation

When you create a Derby database, the attributes that you set determine the collation that is used with all of character data in the database. The following table shows some examples.

Table 1. The create database attributes that control collation
Example Create URLs	Collation Is Driven By
jdbc:derby:abcDB;create=true	Unicode codepoint collation (UCS_BASIC), which is the default collation for Derby databases.
jdbc:derby:abcDB;create=true;territory=es_MX	Unicode codepoint collation (UCS_BASIC). The `collation` attribute is not set.
jdbc:derby:abcDB;create=true;collation=TERRITORY_BASED	The territory of the JVM, since the `territory` attribute is not set. Tip: To determine the territory of the JVM, run `Locale.getDefault()`.
jdbc:derby:abcDB;create=true;territory=es_MX;collation=TERRITORY_BASED	The `territory` attribute.

Collation examples

With Unicode codepoint collation (UCS_BASIC), the numerical values of the Unicode encoding of the characters are used directly for ordering. For example, the FRUIT table contains the NAME column that uses the VARCHAR(20) data type. The contents of the NAME column are:

orange
apple
Banana
Pineapple
Grape

UCS_BASIC collation sorts all lower case letters before upper case letters. The statement SELECT * FROM FRUIT ORDER BY NAME returns:

apple
orange
Banana
Grape
Pineapple

If the database is created with the territory attribute set to en_US (English language, United States country code), and the collation attribute set to TERRITORY_BASED, the results of the statement SELECT * FROM FRUIT ORDER BY NAME returns:

apple
Banana
Grape
orange
Pineapple

The collation set for the database also impacts comparison operators on character data types. For example, the statement SELECT * FROM FRUIT WHERE NAME > 'Banana' ORDER BY NAME returns:

????????????UCS_BASIC collation???????????? Territory-based collation
????????????????????????????????Grape????????????????????????????????????????????????????????????????Grape
????????????????????????????????Pineapple???????????????????????????????????????????????????? orange
????????????????????????????????????????????????????????????????????????????????????????????????????????????????????Pineapple

For information on creating case-insensitive databases, see Creating a database with territory-based collation.

Differences between LIKE and equal (=) comparisons

When you use territory-based collation, the comparisons can return different results when you use the LIKE and equal (=) operators. For example, suppose that the Derby database is set to use a territory where the character 'z' has same collation elements as 'xy'. Consider the following two WHERE clauses:

WHERE 'zcb' = 'xycb'
WHERE 'zcb' LIKE 'xy_b'

For WHERE clause 1, Derby returns TRUE because the collation elements for the entire string 'zcb' will match the collation elements of the entire string 'xycb'.

For WHERE clause 2, Derby returns FALSE because collation element for character 'z' does not match the collation element for character 'x'. In addition, when metacharacter such as an underscore is used with the LIKE operator, the metacharacter counts for one character in the string value. A clause like WHERE 'xycb' LIKE '_cb' returns FALSE because 'x' is compared to the metacharacter _ and 'y' does not match 'c'.