How The Engine Detects The Collation

How the Engine Detects the Collation

The optional FROM clause specifies the base collation that is used to derive a new collation.This collation must already be present in the database.If the keyword EXTERNAL is specified, then Firebird will scan the .conf files in $fbroot/intl/, where extname must exactly match the name in the configuration file (case-sensitive).

If no FROM clause is present, Firebird will scan the .conf file(s) in the intl subdirectory for a collation with the collation name specified in CREATE COLLATION.In other words, omitting the FROM basecoll clause is equivalent to specifying FROM EXTERNAL ('collname').

The — single-quoted — extname is case-sensitive and must correspond exactly with the collation name in the .conf file.The collname, charset and basecoll parameters are case-insensitive unless enclosed in double-quotes.

When creating a collation, you can specify whether trailing spaces are included in the comparison.If the NO PAD clause is specified, trailing spaces are taken into account in the comparison.If the PAD SPACE clause is specified, trailing spaces are ignored in the comparison.

The optional CASE clause allows you to specify whether the comparison is case-sensitive or case-insensitive.

The optional ACCENT clause allows you to specify whether the comparison is accent-sensitive or accent-insensitive (e.g. if ‘'e'’ and ‘'é'’ are considered equal or unequal).

Specific Attributes

The CREATE COLLATION statement can also include specific attributes to configure the collation.The available specific attributes are listed in the table below.Not all specific attributes apply to every collation.If the attribute is not applicable to the collation, but is specified when creating it, it will not cause an error.

Important

Specific attribute names are case-sensitive.

In the table, “1 bpc” indicates that an attribute is valid for collations of character sets using 1 byte per character (so-called narrow character sets), and “UNI” for “Unicode collations”.

Table 1. Specific Collation Attributes
Atrribute	Values	Valid for	Comment
`DISABLE-COMPRESSIONS`	`0`, `1`	1 bpc, UNI	Disables compressions (a.k.a. contractions).Compressions cause certain character sequences to be sorted as atomic units, e.g. Spanish c+h as a single character ch
`DISABLE-EXPANSIONS`	`0`, `1`	1 bpc	Disables expansions.Expansions cause certain characters (e.g. ligatures or umlauted vowels) to be treated as character sequences and sorted accordingly
`ICU-VERSION`	default or M.m	UNI	Specifies the ICU library version to use.Valid values are the ones defined in the applicable `<intl_module>` element in `intl/fbintl.conf`.Format: either the string literal “default” or a major+minor version number like “3.0” (both unquoted).
`LOCALE`	xx_YY	UNI	Specifies the collation locale.Requires complete version of ICU libraries.Format: a locale string like “du_NL” (unquoted)
`MULTI-LEVEL`	`0`, `1`	1 bpc	Uses more than one ordering level
`NUMERIC-SORT`	`0`, `1`	UNI	Treats contiguous groups of decimal digits in the string as atomic units and sorts them numerically.(This is also known as natural sorting)
`SPECIALS-FIRST`	`0`, `1`	1 bpc	Orders special characters (spaces, symbols etc.) before alphanumeric characters

Tip

If you want to add a new character set with its default collation into your database, declare and run the stored procedure sp_register_character_set(name, max_bytes_per_character), found in misc/intl.sql under the Firebird installation directory.

In order for this to work, the character set must be present on the system and registered in a .conf file in the intl subdirectory.