At 03:54 AM 26/10/2004 +0000, Hiro Suyama wrote:

>It has form to create a text field that is not case sensitive?

Yes and no...

The only way to achieve a "non-case-sensitive" field is to force the data
in the item to be upper-cased. This is best done using Before Insert and
Before Update triggers.

Typically, for example, if you want to be able to do case-insensitive
searches on a column, define TWO identical columns, use your triggers to
write the data from the first column into the second, and place your index
on the upper-cased column.

e.g.

create table blah (
.....,
YourCharCol varchar(40),
YourCharColProxy varchar(40),
....
);
commit ^

create trigger BA_blah for blah
active before Insert or Delete
as
begin
if (new.YourCharCol is not null) then
new.YourCharColProxy = upper(YourCharCol);
else
if(updating) then
new.YourCharColProxy = null;
end ^

Note that this strategy won't work for any character set that does not have
upper-to-lower-case mappings for the characters being used. For example,
UNICODE_FSS has mappings only for the US_ASCII equivalent characters.

For other character sets, there may be locale mapping issues that you need
to resolve using a COLLATE in the field definition and in your search
clauses, but I won't go there at this point. :-))

./heLen