Subject Re: [firebird-support] General conventions for Upper/Lower case searching
Author Uwe Grauer
Myles Wakeham wrote:

> I'm trying to determine the general conventions that developers use for
> searching Firebird (or any other SQL database) relating to upper/lower
> case
> searching.
> The data in our search fields will be stored as mixed Upper Case/Lower
> case
> textual data, but when we search for a matching value I'm not sure
> what the
> best strategy is for this. One of our developers wanted to develop a UDF
> for this, but I can't see why this is required.
> If I want to seach a text field for 'Smith', and the values in the
> database
> are referenced as 'SMITH', 'Smith', and 'smith' what is the best way to
> handle this in an application?
> Myles
> ===========================
> Myles Wakeham
> Director of Engineering
> Tech Solutions Inc.
> Scottsdale, Arizona USA
> Phone (480) 451-7440
> Web:
If you want to do a case-insentitive search, you should put the field
all uppercase into a mirror
field for searching. You can do this in an Insert/Update Trigger. If you
use an Index of the mirror
field for searching, you can select with high speeds.
If you do not have much data to be searched, you could also use 'where
upper(tfield) = upper(seachtext)'.