Hi, Helen

Thanks for the quick answer. I'll do it. Storing
proxy fields would be the solution, but it would cause
the database become more bigger (think about if the
client table is at least 1 million records, and we may
need multiple proxy fields like First name, Last name,
... etc), so is it possible that it can be
improved/solved at the database engine level?

Thanks

William

--- Helen Borrie <helebor@...> wrote:

> At 06:49 PM 4/10/2004 +0000, you wrote:
>
>
> >Hi, All
> >
> >I have the following SQL statment intend to search
> the company name
> >beginning with a centain value, I have a index
> based on Name field.
> >
> >SELECT name, street, city, state, postalcode,
> country_id
> >FROM clients
> >WHERE UPPER(name) STARTING WITH :Value;
> >
> >The statment returns all correct records with the
> name starting with
> >"H" or "h" (if the Value is "h"), but DOESN'T use
> the name index, so
> >it is not good.
>
> It can't use the index because UPPER(name) is an
> expression.
>
>
> >What is the best way to to make the firebird
> database engine to pick
> >the correct index (NAME) to do such kind of
> searching regardless if
> >the searched fields are uppercase or lowercase.
>
> Store a proxy search column of the same size as Name
> and write insert and
> update triggers to populate it, e.g.
>
> new.ProxyName = upper(Name);
>
> Index the proxy column instead of the Name column.
>
> ./heLen
>
>

=====
William, Yu




__________________________________
Do you Yahoo!?
Take Yahoo! Mail with you! Get it on your mobile phone.
http://mobile.yahoo.com/maildemo