Firebird 2.5 Language ReferenceFirebird 2.5 Language ReferenceData Definition (DDL) Statements → COLLATION
Firebird Firebird Prev: EXCEPTIONFirebird 2.5 Language ReferenceUp: Data Definition (DDL) StatementsNext: CHARACTER SET

COLLATION

Table of Contents

CREATE COLLATION
DROP COLLATION

CREATE COLLATION

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

Available in: DSQL

Syntax: 

CREATE COLLATION collname
FOR charset
[FROM basecoll | FROM EXTERNAL ('extname')]
[NO PAD | PAD SPACE]
[CASE [IN]SENSITIVE]
[ACCENT [IN]SENSITIVE]
['<specific-attributes>'];

<specific-attributes> ::= <attribute> [; <attribute> ...]

<attribute> ::= attrname=attrvalue
        

Table 5.41. CREATE COLLATION Statement Parameters

Parameter Description
collname The name to use for the new collation. The maximum length is 31 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.

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.

[Important] Important

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

Table 5.42. Specific Collation Attributes

Atrribute Values Valid for Comment
DISABLE-COMPRESSIONS 0, 1 1 bpc 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] 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.

Note: 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).
    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).
    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 also: DROP COLLATION

DROP COLLATION

Used for:  Removing a collation from the database

Available in: DSQL

Syntax: 

DROP COLLATION collname
        

Table 5.43. DROP COLLATION Statement Parameters

Parameter Description
collname The name of the collation


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

[Tip] 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.

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.

DROP COLLATION ES_ES_NOPAD_CI;
          

See also: CREATE COLLATION

Prev: EXCEPTIONFirebird 2.5 Language ReferenceUp: Data Definition (DDL) StatementsNext: CHARACTER SET
Firebird 2.5 Language ReferenceFirebird 2.5 Language ReferenceData Definition (DDL) Statements → COLLATION