--- In firebird-support@yahoogroups.com, Alexandre Benson Smith <iblist@...> wrote:

>
> bwc3068 wrote:
> > Hi--
> >
> > A simple question on making my where clauses case insensitive with FB 2.nn.
> >
> > is there an easier way then setting up the SQL like the below?
> >
> > select * from table
> > where
> > upper(field) like '%UPPERCASE LOOK FOR%'
> > order by
> > upper(field)
> >
>
> use a case insensitive collation such as PT_BR for ISO8859_1 charset
>

Exactly i also use PT_BR collation with the ISO8859_1 charset, and it does the trick, its a case insensitive accent sensitive collation.

> > is there a speed issue?
>
> if you have a expression index on UPPER(Column) then it would be ok, it
> you don't have such an index FB would use a naturl table scan, wich
> could be slow in large tables
>

Yes, it all comes down to indexes...

> > that is...will that resolve as fast as:
> >
> > select * from table
> > where
> > field like '%LOOK FOR%'
> > order by
> > field
> >
>
> It will be as fast (or better as slow), because both would not use an index.
>
> > thanks again!
> > regards
> > kelly
> >
>
> see you !
>
> --
> Alexandre Benson Smith
> Development
> THOR Software e Comercial Ltda
> Santo Andre - Sao Paulo - Brazil
> www.thorsoftware.com.br
>