Firebird - Case Insensitive Queries
Firebird dose not support case insensitive queries.
Consider these options
select * from "abc_table" where "Some_Field" = 'Abc'
select * from "abc_table" where "Some_Field" like 'Abc'
select * from "abc_table" where "Some_Field" containing 'Abc'
select * from "abc_table" where upper("Some_Field") = 'ABC'
- Equals (=) and *like* both perform case sensitive matches
- *containing* is case insensitive, but will also match 'abcd'
- upper() works, but will not use an index and, therefore, will read every record in the table
- Equals (=) is the fastest because it uses an index (if available)
One solution is to create a special field - "Some_Field_Upper" -
and a trigger that automatically saves the upper case value.
Then you just query the using the computed value.
This example is from
Triggers in Firebird
which provides a very good description of triggers.
CREATE OR ALTER TRIGGER BIU_EMPLOYEES FOR EMPLOYEES
BEFORE INSERT OR UPDATE AS
NEW.LASTNAME_UPPER = UPPER (NEW.LASTNAME);
Author: Robert Clemenzi -