Mimer SQL Documentation TOC PREV NEXT INDEX

Mimer SQL Developer Site


Using Collations


You can specify a collation for ordering characters when you create or alter a table or create a domain or a user-defined type.

If you have specified a collation for a column, the collation is used implicitly in SQL statements.

You only need to explicitly use a collate clause in SQL statements if you want to override the default collation or the collation you specified when creating or altering the table or creating the domain or user-defined type.

Character Strings

SQL only permits compatible character strings to be compared. That is, you can compare character strings only if the source and target strings belong to the same collation or can be coerced into having the same collation.

A character string that is defined with a named collation can only be compared or assigned to a character string that is either defined with the same named collation or is defined without a collation.

In the case where one of the strings is not associated with a named collation then it will be implicitly coerced to the same collation as the other string.

String Comparison Examples

The following three comparisons are all legal (and equivalent):

 job_title = 'developer' COLLATE english_1
 
 job_title COLLATE english_1 = 'developer'
 
 job_title COLLATE english_1 = 'developer' COLLATE english_1
 

But -

 job_title COLLATE english_1 = 'developer' COLLATE swedish_1
 

is illegal because different collations are specified.

CREATE/ALTER TABLE

When creating or altering a table, you can specify a collation in the column-definition, for example:

 CREATE TABLE employees (surname CHAR(20) COLLATE swedish_1
              ...

CREATE DOMAIN and CREATE TYPE

When creating a domain or a user-defined type, you can specify a collation for the character and national character string data types, for example:

 CREATE DOMAIN name_type AS VARCHAR(48) COLLATE english_1;
 CREATE TYPE car_models AS VARCHAR(48) COLLATE english_1;
 

All properties of a domain or a user-defined type apply to the column when the domain/type is used in a CREATE TABLE or ALTER TABLE statement.

CREATE INDEX

To improve performance when retrieving data, you can create more than one index for a column using different collations, for example:

 CREATE INDEX cnt_eng_ind ON countries (country COLLATE english_3);
 CREATE INDEX cnt_swe_ind ON countries (country COLLATE swedish_3);
 

Which index that will be used depends on the situation. For example:

 SELECT * FROM countries ORDER BY country COLLATE english_3;
 

will use the cnt_eng_ind index.

And

 SELECT * FROM countries ORDER BY country COLLATE swedish_3;
 

will use the cnt_swe_ind index.

Collation Precedence

A collation specified in the column-definition will take precedence over a domain or user-defined type collation.

Continuing with the example above, the domain collation was set to english_1, but in the following example the column country is set to swedish_1, which takes precedence over the domain setting:

       CREATE TABLE countries (
       code CHARACTER(2),
       country name_type COLLATE swedish_1,
       ...

Altering Collations on Columns

You can change the collation specified for a column by using the ALTER TABLE statement, for example:

 ALTER TABLE countries ALTER COLUMN country CHAR(20) COLLATE english_1;
 

To return to the default (ISO8BIT) sorting order, you would enter:

 ALTER TABLE countries ALTER COLUMN country CHAR(20) COLLATE ISO8BIT;
 

By altering a collation, for example to the default ISO8BIT collation, you can remove any dependencies associated with the collation. This makes it possible to drop the collation - see the next section.

Dropping a Collation

You can drop a collation only if there are no dependencies, for example:

 DROP COLLATION collation_name RESTRICT;

Finding Out the Default Collation For a Column

You can find out which collation a column uses by reviewing the INFORMATION_SCHEMA.COLUMNS view, for example:
 SELECT *
    FROM INFORMATION_SCHEMA.COLUMNS
    WHERE table_name = 'table1'
    AND column_name = 'col1';
 

For more information, see the Mimer SQL Reference Manual, INFORMATION_SCHEMA.


Mimer
Mimer Information Technology AB
Voice: +46 18 780 92 00
Fax: +46 18 780 92 40
info@mimer.se
Mimer SQL Documentation TOC PREV NEXT INDEX