Databases - Delphi/Firebird - Case Insensitive Queries

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
BEGIN
  NEW.LASTNAME_UPPER = UPPER (NEW.LASTNAME);
END;

Author: Robert Clemenzi - clemenzi@cpcug.org

Firebird - Case Insensitive Queries

URL: http:// cpcug.org / user / clemenzi / technical / Databases / Delphi / Firebird / Query_Case_Insensitive.html