5.13.1. CREATE COLLATION

    Making a new collation for a supported character set available to the database

    Available in

    DSQL

    Syntax

    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.

    How the Engine Detects the Collation

    If no FROM clause is present, Firebird will scan the .conf file(s) in the intl subdirectory for a collation with the name specified as the object of 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.

    Specific Attributes

    The available specific attributes are listed in the table below. Not all specific attributes apply to every collation, even if specifying them does not cause an error.

    Specific attributes 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). “UNI” stands for “UNICODE collations”.

    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.

    Any user connected to the database can use CREATE COLLATION to add a new collation.

    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 case-insensitive collation based on one already existing in the database.

      1. CREATE COLLATION ES_ES_NOPAD_CI
      3.   FROM ES_ES
      4.   NO PAD
      5.   CASE INSENSITIVE;

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

    See also

    5.13.2. DROP COLLATION

    Used for

    Removing a collation from the database

    Available in

    DSQL

    Syntax

    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.

    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.

    Any user connected to the database can use DROP COLLATION to remove a collation.

    Example using DROP COLLATION

    Deleting the ES_ES_NOPAD_CI collation.

    1. DROP COLLATION ES_ES_NOPAD_CI;

    See also