Specifying Collation Orders
Go Up to Character Sets and Collation Orders
This section describes how to use the COLLATE
clause to specify collation order in columns, comparison operations, ORDER BY
clauses, and GROUP BY
clauses.
Contents
Specifying Collation Order for a Column
Use the COLLATE
clause with either CREATE TABLE
or ALTER TABLE
to specify the collation order for a CHAR
or VARCHAR
column. The COLLATE
clause is especially useful for character sets such as ISO8859_1 or DOS437 that support many different collation orders.
For example, the following isql ALTER TABLE
statement adds a new column to a table, and specifies both a character set and a collation order:
ALTER TABLE 'FR_CA_EMP'
ADD ADDRESS VARCHAR(40) CHARACTER SET ISO8859_1
NOT NULL
COLLATE FR_CA;
For the complete syntax of ALTER TABLE
, see Language Reference Guide.
Specifying Collation Order in a Comparison Operation
When CHAR
or VARCHAR
values are compared in a WHERE
clause, it is necessary to specify a collation order for the comparisons if the values being compared use different collation orders.
To specify the collation order to use for a value during a comparison, include a COLLATE
clause after the value. For example, in the following WHERE
clause fragment from an embedded application, the value to the left of the comparison operator is forced to be compared using a specific collation:
WHERE LNAME COLLATE FR_CA = :lname_search;
For the complete syntax of the WHERE
clause, see Language Reference Guide.
Specifying Collation Order in an ORDER BY Clause
When CHAR
or VARCHAR
columns are ordered in a SELECT
statement, it can be necessary to specify a collation order for the ordering, especially if columns used for ordering use different collation orders.
To specify the collation order to use for ordering a column in the ORDER BY
clause, include a COLLATE
clause after the column name. For example, in the following ORDER BY
clause, the collation order for two columns is specified:
. . .
ORDER BY LNAME COLLATE FR_CA, FNAME COLLATE FR_CA;
For the complete syntax of the ORDER BY
clause, see the Language Reference Guide.
Specifying Collation Order in a GROUP BY Clause
When CHAR
or VARCHAR
columns are grouped in a SELECT
statement, it can be necessary to specify a collation order for the grouping, especially if columns used for grouping use different collation orders.
To specify the collation order to use for grouping columns in the GROUP BY
clause, include a COLLATE
clause after the column name. For example, in the following GROUP
BY
clause, the collation order for two columns is specified:
. . .
GROUP BY LNAME COLLATE FR_CA, FNAME COLLATE FR_CA;
For the complete syntax of the GROUP BY
clause, see Language Reference Guide.