Collate

<< Clustering | IBExpert Glossary | Column >>

Collations define the sort ordering for things like ORDER BY. Collations also define the casing rules for things like UPPER() and LOWER().

A special collation sequence can be specified for CHAR and VARCHAR field columns; the COLLATE parameter allows fields to be collated according to a certain language/group of languages e.g. collate according to the German language when using Win1252. A full list of collations for database version used, can be found in the system table, RDB$COLLATIONS (the RDB$CHARACTER_SET_ID refers for which character set the collations are for.

In IBExpert the collation sequence can be specified when defining the character set for a domain or field:

The collation options are offered in IBExpert in a drop-down list, after specifying the character set.

In DDL it is specified using the keyword COLLATE and the respective character set table, for example:

 CREATE DOMAIN dom_city VARCHAR(20)
 COLLATE PXW_INTL850;

 CREATE DOMAIN User_Name VARCHAR(20)
 CHARACTER SET DOS437
 DEFAULT USER
 NOT NULL
 COLLATE PDOX_ASCII

The parameter sequence is important, as the collation sequence must be specified last.

Since Firebird 2.5, there is also a collation option for numerals (only in Unicode collation):

 NUMERIC-SORT={0 | 1}

The default, 0, sorts numerals in alphabetical order:

 1
 10
 100
 2
 20

The parameter, 1, sorts numerals in numerical order:

 1
 2
 10
 20
 100

See also:
CHAR and VARCHAR
NCHAR and NVARCHAR
COLLATE
CREATE COLLATION (Firebird 2.1)
New collations in Firebird 2.1
New collations in Firebird 2
Firebird 2.1 Release Notes: International language support (INTL)
Firebird 2.1 Release Notes: Appendix B: International character sets

back to top of page
<< Clustering | IBExpert Glossary | Column >>