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