Mimer SQL Documentation TOC PREV NEXT INDEX

Mimer SQL Developer Site


Using Collations


You can specify a different collation for ordering characters when you create or alter a table or create a domain.

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

You only need to explicitly use a collation 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.

Character Strings

SQL only permits compatible character strings to be assigned or compared. That is, you can assign or 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 the collations have different names.

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

When creating a domain, 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;
 

All properties of a domain apply to the column when the domain 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_1);
 CREATE INDEX cnt_swe_ind ON countries (country COLLATE swedish_1);

Collation Precedence

A collation specified in the column-definition will take precedence over a domain 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 name 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