Firebird Documentation IndexFirebird 2.5 Language Ref. UpdateDDL statements → COLLATION
Firebird Home Firebird Home Prev: CHARACTER SETFirebird Documentation IndexUp: DDL statementsNext: COMMENT

COLLATION

CREATE COLLATION
DROP COLLATION

CREATE COLLATION

Available in: DSQL

Added in: 2.1

Changed in: 2.5

Description: Adds a collation to the database. The collation must already be present on your system (typically in a library file) and must be properly registered in a .conf file in the intl subdirectory of your Firebird installation. You may also base the collation on one that is already present in the database.

Syntax: 

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

collname               ::=  the name to use for the new collation
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
<specific-attributes>  ::=  <attribute> [; <attribute> ...]
<attribute>            ::=  attrname=attrvalue
  • If no FROM clause is present, Firebird will scan the .conf file(s) in your intl subdirectory for a collation with the name specified after CREATE COLLATION. That is, omitting the FROM clause is the same as specifying “FROM EXTERNAL ('collname')”.

  • The single-quoted extname is case-sensitive and must be exactly equal to the collation name in the .conf file. The collname, charset and basecoll parameters are case-insensitive, unless surrounded by double-quotes.

Specific attributes: The table below lists the available specific attributes. Not all specific attributes apply to every collation, even if specifying them doesn't cause an error. Please note that specific attributes are case sensitive. In the table below, “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 6.1. Specific collation attributes

Name Values Valid for Comment
DISABLE-COMPRESSIONS 0, 1 1 bpc Disables compressions (aka 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.


Note: The NUMERIC-SORT specific attribute was added in Firebird 2.5.

Examples: 

Simplest form, using the name as found in the .conf file (case-insensitive):

create collation iso8859_1_unicode for iso8859_1

Using a custom name. Notice how the “external” name must now exactly match the name in the .conf file:

create collation lat_uni
  for iso8859_1
  from external ('ISO8859_1_UNICODE')

Based on a collation already present in the database:

create collation es_es_nopad_ci
  for iso8859_1
  from es_es
  no pad
  case insensitive

With a special attribute (case-sensitive!):

create collation es_es_ci_compr
  for iso8859_1
  from es_es
  case insensitive
  'DISABLE-COMPRESSIONS=0'

Tip

If you want to add a new character set with its default collation in your database, declare and run the stored procedure sp_register_character_set(name, max_bytes_per_character), found in misc/intl.sql under your Firebird installation directory. Please note: in order for this to work, the character set must be present on your system and registered in a .conf file in the intl subdirectory.

DROP COLLATION

Available in: DSQL

Added in: 2.1

Description: Removes a collation from the database. Only user-added collations can be removed in this way.

Syntax: 

DROP COLLATION name

Tip

If you want to remove an entire character set with all its collations from your database, declare and run the stored procedure sp_unregister_character_set(name), found in misc/intl.sql under your Firebird installation directory.

Prev: CHARACTER SETFirebird Documentation IndexUp: DDL statementsNext: COMMENT
Firebird Documentation IndexFirebird 2.5 Language Ref. UpdateDDL statements → COLLATION