> > > Are there any case insensitive collations in firebird 1.5 or 2?
> > > I am wanting to do case insensitive searches. While I can do this
> > > with Upper, it doesn't use any indexes so it's not very efficient.
> >
> > With Firebird 1.5 you can create a surrogate 'upper' field
> > and use before insert or update triggers to maintain it. You
> > can then index this surrogate field and do a 'case
> > insensitive' search against it.
> > Not ideal but workable.
> >
> > With Firebird 2, you can use an expression index. For
> > example, you can create an index on Upper(Field1) so that the
> > following query will use that index
> >
> >
> > select *
> > from table1
> > where Upper(field1) = 'HELLO WORLD'
> >
> > Adam
> >
> >
>
> The first one is a bit of a nasty hack.

What is so nasty about it? It takes up more storage true, and for
longer varchar fields you will probably want to use a hash instead,
but it is a technique that works out of the box in FB 1.5 with little
work.

> The second is an improvement.

Obviously if they thought that the first suggestion (which also works
in FB2) was the best possible solution, then they would not have added
expression indices. FB2 is of course beta (soon to be RC) so it is not
yet considered stable enough for production use, but is available for
you to test against.

> However I think a case insesitive collation would be a better

answer. Is

> there a reason why this has never been added. There seems to be a fair
> demand for it.

My guess is that there are more important things to do than tidying up
some workaround that works fine.

We will continue to use the first approach until our customers
eventually migrate to FB 2 (or 3 or 4 or whatever).

Adam