# 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.

Parent topic: Character-based collation in Derby
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