FirebirdSQL logo

Collation

Each character set has a default collation (COLLATE) that specifies the collation order (or, collation sequence, or collating sequence).Usually, it provides nothing more than ordering based on the numeric code of the characters and a basic mapping of upper- and lower-case characters.If some behaviour is needed for strings that is not provided by the default collation and a suitable alternative collation is supported for that character set, a COLLATE collation clause can be specified in the column definition.

A COLLATE collation clause can be applied in other contexts besides the column definition.For comparison operations, it can be added in the WHERE clause of a SELECT statement.If output needs to be sorted in a special alphabetic sequence, or case-insensitively, and the appropriate collation exists, then a COLLATE clause can be included with the ORDER BY clause when rows are being sorted on a character field and with the GROUP BY clause in case of grouping operations.

Case-Insensitive Searching

For a case-insensitive search, the UPPER function could be used to convert both the search argument and the searched strings to upper-case before attempting a match:

...
where upper(name) = upper(:flt_name)

For strings in a character set that has a case-insensitive collation available, you can apply the collation, to compare the search argument and the searched strings directly.For example, using the WIN1251 character set, the collation PXW_CYRL is case-insensitive for this purpose:

...
WHERE FIRST_NAME COLLATE PXW_CYRL >= :FLT_NAME
...
ORDER BY NAME COLLATE PXW_CYRL
See also

CONTAINING