5.16 COLLATION

In SQL, text strings are sortable objects. This means that they obey ordering rules, such as alphabetical order. Comparison operations can be applied to such text strings (for example, less than or greater than), where the comparison must apply a certain sort order or collation. For example, the expression 'a' < 'b' means that 'a' precedes 'b' in the collation. The expression 'c' > 'b' means that 'c' follows 'b' in the collation. Text strings of more than one character are sorted using sequential character comparisons: first the first characters of the two strings are compared, then the second characters, and so on, until a difference is found between the two strings. This difference defines the sort order.

A COLLATION is the schema object that defines a collation (or sort order).

5.16.1 CREATE COLLATION

Used forCreating a new collation for a supported character set available to the database

Available inDSQL

Syntax

  1. CREATE COLLATION collname
  2. FOR charset
  3. [FROM {basecoll | EXTERNAL ('extname')}]
  4. [NO PAD | PAD SPACE]
  5. [CASE [IN]SENSITIVE]
  6. [ACCENT [IN]SENSITIVE]
  7. ['<specific-attributes>']
  8. <specific-attributes> ::= <attribute> [; <attribute> ...]
  9. <attribute> ::= attrname=attrvalue

Table 5.16.1.1 CREATE COLLATION Statement Parameters

ParameterDescription

collname

The name to use for the new collation. The maximum length is 63 characters

charset

A character set present in the database

basecoll

A collation already present in the database

extname

The collation name used in the .conf file

The CREATE COLLATION statement does not create anything, its purpose is to make a collation known to a database. The collation must already be present on the system, typically in a library file, and must be properly registered in a .conf file in the intl subdirectory of the Firebird installation.

The collation may alternatively be based on one that is already present in the database.

5.16.1.1 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).

5.16.1.2 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 5.16.1.2.1 Specific Collation Attributes

AtrributeValuesValid forComment

DISABLE-COMPRESSIONS

0, 1

1 bpc, UNI (4.0.1+)

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)

Since Firebird 4.0.1 this option also supports ICU locale keywords and attributes.

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.

5.16.1.3 Who Can Create a Collation

The CREATE COLLATION statement can be executed by:

The user executing the CREATE COLLATION statement becomes the owner of the collation.

5.16.1.4 Examples using CREATE COLLATION

  1. Creating a collation using the name found in the fbintl.conf file (case-sensitive)

    1. CREATE COLLATION ISO8859_1_UNICODE FOR ISO8859_1;
  2. Creating a collation using a special (user-defined) name (the external name must completely match the name in the fbintl.conf file)

    1. CREATE COLLATION LAT_UNI
    2. FOR ISO8859_1
    3. FROM EXTERNAL ('ISO8859_1_UNICODE');
  3. Creating a case-insensitive collation based on one already existing in the database

    1. CREATE COLLATION ES_ES_NOPAD_CI
    2. FOR ISO8859_1
    3. FROM ES_ES
    4. NO PAD
    5. CASE INSENSITIVE;
  4. Creating a case-insensitive collation based on one already existing in the database with specific attributes

    1. CREATE COLLATION ES_ES_CI_COMPR
    2. FOR ISO8859_1
    3. FROM ES_ES
    4. CASE INSENSITIVE
    5. 'DISABLE-COMPRESSIONS=0';
  5. Creating a case-insensitive collation by the value of numbers (the so-called natural collation)

    1. CREATE COLLATION nums_coll FOR UTF8
    2. FROM UNICODE
    3. CASE INSENSITIVE 'NUMERIC-SORT=1';
    4. CREATE DOMAIN dm_nums AS varchar(20)
    5. CHARACTER SET UTF8 COLLATE nums_coll; -- original (manufacturer) numbers
    6. CREATE TABLE wares(id int primary key, articul dm_nums ...);

See alsoSection 5.16.2, DROP COLLATION

5.16.2 DROP COLLATION

Used forRemoving a collation from the database

Available inDSQL

Syntax

  1. DROP COLLATION collname

Table 5.16.2.1 DROP COLLATION Statement Parameters

ParameterDescription

collname

The name of the collation

The DROP COLLATION statement removes the specified collation from the database, if it exists. An error will be raised if the specified collation is not present.

Tip

If you want to remove an entire character set with all its collations from the database, declare and execute the stored procedure sp_unregister_character_set(name) from the misc/intl.sql subdirectory of the Firebird installation.

5.16.2.1 Who Can Drop a Collation

The Drop COLLATION statement can be executed by:

  • Administrators

  • The owner of the collation

  • Users with the DROP ANY COLLATION privilege

5.16.2.2 Example using DROP COLLATION

Deleting the ES_ES_NOPAD_CI collation.

  1. DROP COLLATION ES_ES_NOPAD_CI;

See alsoSection 5.16.1, CREATE COLLATION