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

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.

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.

The CREATE COLLATION statement can be executed by:

• Administrators

• Users with the CREATE COLLATION privilege

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

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

   CREATE COLLATION ISO8859_1_UNICODE FOR ISO8859_1;

2. Creating a collation using a special (user-defined) name (the external name must match the name in the fbintl.conf file)

   CREATE COLLATION LAT_UNI
     FOR ISO8859_1
     FROM EXTERNAL ('ISO8859_1_UNICODE');

3. Creating a case-insensitive collation based on one already existing in the database

   CREATE COLLATION ES_ES_NOPAD_CI
     FOR ISO8859_1
     FROM ES_ES
     NO PAD
     CASE INSENSITIVE;

4. Creating a case-insensitive collation based on one already existing in the database with specific attributes

   CREATE COLLATION ES_ES_CI_COMPR
     FOR ISO8859_1
     FROM ES_ES
     CASE INSENSITIVE
     'DISABLE-COMPRESSIONS=0';

5. Creating a case-insensitive collation by the value of numbers (the so-called natural collation)

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

See alsoSection 5.16.2, “DROP COLLATION”

The Drop COLLATION statement can be executed by:

• Administrators

• The owner of the collation

• Users with the DROP ANY COLLATION privilege

Deleting the ES_ES_NOPAD_CI collation.

DROP COLLATION ES_ES_NOPAD_CI;

See alsoSection 5.16.1, “CREATE COLLATION”