Specifying Collation Orders

From InterBase

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.


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.

Advance To: