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. collationexamples locale-based collationexamples connection URL attributesexamples of locale-based collation

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 .