Hi Darryl,

A work-around that's being used _very_ often is quite simple:

Create a "shadow" column and a trigger that fills this column with
the upper-case equivalent of your "search" column.

If you tend to search a lot on a column, for example, LAST_NAME
create a U_LAST_NAME column and create an index on the column.
Next, use WHERE U_LAST_NAME = 'TONI%' etc...

Firebird Workbench even has a special dialog for this in the Table
Editor - it creates the triggers for you!


With regards,

Martijn Tonies
InterBase Workbench - the developer tool for InterBase & Firebird
Firebird Workbench - the developer tool for Firebird
Upscene Productions
http://www.upscene.com

"This is an object-oriented system.
If we change anything, the users object."

> I tried to research this problem earlier but could find no good
> solution for doing case-insensitive searching using Firebird on a
> Linux box. This is possible on Windows machines because as noted in
> the other posts, a case-insensitive collation has been developed for
> that platform. If you want to accomplish this on a Linux box, you're
> left with two options:
>
> 1. Lose indexing, sorting and do table scans.
> 2. Duplicate data and cast it to uppercase so you can index it.
> Anytime you want to index alpha-numeric fields, you have to add
> additional fields to your table to hold upper case copies of the
> indexed fields.
>
> Here's a link about the problem on Borland's website:
> http://bdn.borland.com/article/0,1410,25171,00.html
>
> Being familiar with MSSQL and MySQL, I was surprised (stunned) to find
> out that Firebird did not have the capability to do case-insensitive
> searching using an index. From what I can see, if you want
> case-insensitivity on a Linux box you have to choose between giving up
> performance or writing very unportable client / database code. I keep
> hoping someone will tell me I'm wrong because I'm impressed by
> Firebird's other features.
>
> Darryl
>
> --- In ib-support@yahoogroups.com, "Kumar" <manuelf001@h...> wrote:
> > Aloha Again... Thanks so much for the advice given so far. I've
> downloaded more manuals than I've time to read, but am making a lot of
> progress on identifying the conversion (DBISAM -> FB) issues.
> >
> > In DBISAM, there is the ability to indicate that an index is not
> case sensitive. Thus, "Abc" would be right next to "ABC" rather than
> far apart in the index, the thus (I believe) optimize a query on the
> field.
> >
> > I can't seem to find such an animal in FB. Would something this work:?
> >
> > Select * from Clients where Upper(Clients.Name) like 'SMITH%'
> >
> > TIA,
> > -k-
> >
> >
> >
> >
> >
> > [Non-text portions of this message have been removed]
>
>
>
> To unsubscribe from this group, send an email to:
> ib-support-unsubscribe@egroups.com
>
>
>
> Your use of Yahoo! Groups is subject to http://docs.yahoo.com/info/terms/
>
>
>