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.