FirebirdSQL logo
 SQL Language StructureCommon Language Elements 

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

UTF8 Collations

The following table shows the possible collations for the UTF8 character set.

Table 1. Collations for Character Set UTF8
Collation Characteristics

UCS_BASIC

Collation works according to the position of the character in the table (binary).

UNICODE

Collation works according to the UCA algorithm (Unicode Collation Algorithm) (alphabetical).

UTF8

The default, binary collation, identical to UCS_BASIC, which was added for SQL compatibility

UNICODE_CI

Case-insensitive collation, works without taking character case into account.

UNICODE_CI_AI

Case-insensitive, accent-insensitive collation, works alphabetically without taking character case or accents into account.
Example

An example of collation for the UTF8 character set without taking into account the case or accentuation of characters (similar to COLLATE PXW_CYRL in the earlier example).

...
ORDER BY NAME COLLATE UNICODE_CI_AI