You can specify a 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 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.
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.
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 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 apply to the column when the domain/type is used in a
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
AndSELECT * FROM countries ORDER BY country COLLATE swedish_3;
will use the
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
countryis 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 TABLEstatement, 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
ISO8BITcollation, 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 ColumnYou can find out which collation a column uses by reviewing the
INFORMATION_SCHEMA.COLUMNSview, 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 Information Technology AB
Phone: +46 18 780 92 00