Mimer SQL Documentation TOC PREV NEXT INDEX

Mimer SQL Developer Site

http://developer.mimer.com


Using Collations


By default, characters are sorted in the numerical order of their codes according to ISO 8859-1. In Mimer SQL, ISO 8859-1 is referred to as ISO8BIT.

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
 
 job_title COLLATE english = 'developer'
 
 job_title COLLATE english = 'developer' COLLATE english
 

But -

 job_title COLLATE english = 'developer' COLLATE swedish
 

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

CREATE DOMAIN

When creating a domain, you can specify a collation for the character string data types CHAR, VARCHAR and CLOB, for example:

 CREATE DOMAIN name AS VARCHAR(48) COLLATE ENGLISH;
 

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 EMGLISH);
 CREATE INDEX cnt_swe_ind ON countries (country COLLATE SWEDISH);

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, but in the following example the column surname is set to swedish, which takes precedence over the domain setting:

       CREATE TABLE countries (
       code CHARACTER(2),
       country name COLLATE SWEDISH,
       ...

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;
 

To return Mimer SQL's 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;

What collation is used on a column?

You can find out which collation a column uses by reviewing INFORMATION_SCHEMA, for example:

 SELECT *
    FROM INFORMATION_SCHEMA.COLUMNS
    WHERE table_name = 'table1'
    AND column_name = 'col1';
 

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



Upright Database Technology AB
Voice: +46 18 780 92 00
Fax: +46 18 780 92 40
dbtechnology@upright.se
Mimer SQL Documentation TOC PREV NEXT INDEX