--- In firebird-support@yahoogroups.com, "diegodelafuente"
<diegodelafuente@y...> wrote:

>
> --- In firebird-support@yahoogroups.com, Carsten Schäfer
> <ca_schaefer@g...> wrote:
> >
> > Hi,
> > will there be case insensitive queries that can use an index in
> Firebird 3 ?
> > I often see questions about this issue in this newsgroup and now i
> want
> > to know if this problem will be adressed in the next future ?
> > I think it would be a very useful feature.
> >
> > mfg
> > Carsten
> >
>
> You can use a work arround in firebird 1.5.
> if you hace a field called "name", make a new one
> called "name_upper".
> run a Sql command like "update 'table' set name_upper = upper(name);"
> And then in the insert procedure you can add tha value to the
> field "name" and the same value with upper("value") to
> the "name_upper" field.
> the you have thist field with all Upper values and you can run a
> case insensitive search in thist field (you must apply an index to
> thist field)
> That works fine, but you must note that you must duplicate the the
> fields using more space in the DB.
>

A better way is to use a trigger

create trigger testtrigger for test
active before insert or update
as
begin
new.name_upper = Upper(new.name);
end
^

Obviously you need the index on name_upper, but with this method you
can otherwise ignore name_upper in your insert and update statements
which means your old code should still work if you haven't been lazy
with your insert syntax (ie specifically declare each field).

Adam