Subject Re: [ib-support] How to index on UPPER()? or some other solution
Author Luiz
You could create and use an additional column to save the uppercase value,
filling it by triggers and indexing it for your needs or you could give a
look in IBCollation by David Schnepper on

----- Original Message -----
From: "Chuck Belanger" <phytotech@...>
To: "IB-Support Group" <>
Sent: Saturday, September 07, 2002 6:00 PM
Subject: [ib-support] How to index on UPPER()? or some other solution

> Hello:
> I feel like I have a simple problem and hope someone can help:
> I'm trying to do fairly straight forward SQL statement in IB, but its
> incredibly slow (in local mode, too), because I need an index on
> UPPER(field_name), but in IB it seems that I can't do that. All I can do
> is add a new field and keep this all upper case.
> There must be an easier way! Is there?
> Something about adding UDFs? (never attempted to do this, btw).
> Here's the SQL if you think it would help:
> Its querying a self-referencing table. The param StrToCheck is all upper
> case. There are indexes on everything, except the UPPER(item_name).
> Thank you,
> Chuck Belanger
> Richmond, CA
> SQL.clear;
> SQL.Add('select v1.root_parent_id,');
> SQL.Add(' v1.parent_id,');
> SQL.Add(' v2.item_name as Root,');
> SQL.Add(' v3.item_name as Parent,');
> SQL.Add('v1.proprietary ');
> SQL.Add('from virtuallibrary v1');
> SQL.Add(' join virtuallibrary v2 on');
> SQL.Add('v1.root_parent_id = v2.item_id ');
> SQL.Add(' join virtuallibrary v3 on');
> SQL.Add('v1.parent_id = v3.item_id ');
> SQL.Add('where UPPER(v1.item_name)=:StrToCheck');
> KeyLinks.Clear;
> KeyLinks.Add('root_parent_id = item_id ');
> KeyLinks.Add('parent_id = item_id ');
> prepared := true;