Examples of case-sensitive and case-insensitive string sorting

These examples show the results of sorts on databases created with various collation and locale attributes.

With Unicode codepoint collation (UCS_BASIC), the default if you do not specify either collation=collation or territory=ll_CC, the numeric 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 uppercase letters before lowercase letters. The statement SELECT * FROM FRUIT ORDER BY NAME returns the following:
  • Banana
  • Grape
  • Pineapple
  • apple
  • orange

The above result also appears if you specify territory=ll_CC but do not specify collation=collation.

If the database is created with the territory=ll_CC attribute set to en_US (English language, United States country code) and the collation=collation attribute set to TERRITORY_BASED, 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 Locale-based collation
Grape Grape
Pineapple orange
apple Pineapple
orange  

For information on creating case-insensitive databases, see Creating a case-insensitive database.

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