Stefan
Here is what I had in mind and that I confirmed:
You need a function for each data type, but not including the length. Also CHAR and VCHAR combine well, i.e you can define your function inbound and outbound as varchar (255) and it will work with index on a CHAR, up to 255.
On the other hand, using a "char" defined function for a varchar also works
So the real limits seems to be the limits of an index key (i.e 387 bytes for 2k pages etc).
Conclusion: You have the choice either to choose 'char' defined or 'varchar' defined inbound and outbound, and your limit is the size of your page, ie 387B for 2 K
Here is a small script with which you can play to check all this. Don't forget to check the set explain on output
BR
Eric
drop table if exists Persons;
CREATE TABLE Persons (
LastName varchar(255,30),
FirstName varchar(128,20),
Address varchar(255),
City char(50),
info lvarchar(500),
id serial
);
drop function if exists my_lower_varchar;
create function if not exists my_lower_varchar( s varchar(255) ) returning varchar(255) with (not variant )
return trim( lower(s)) ; end function;
drop function if exists my_lower_char;
create function if not exists my_lower_char( s char(387) ) returning char(387) with (not variant )
return trim( lower(s)) ; end function;
drop function if exists my_lower_lvarchar;
create function if not exists my_lower_lvarchar( s lvarchar(4096) ) returning lvarchar(4096) with (not variant )
return trim(lower(s)) ; end function;
insert into persons values ("King","Laura","Main Street 667","Boston","Laura is one of the founders of RDS, the Informix 4GL ancestor",0);
insert into persons values ("Sippl","Roger","Bohannon Drive 4100","Menlo Park","Roger founded Informix jointly with Laura",0);
insert into persons values ("Saranga","Mike","2nd avenue 3200 ","Portland","Mike designed the new multi-threaded architecture of Informix Dynamic Server",0);
insert into persons values ("White","Phil","Bohannon Drive 4100","Menlo Park","Phil is a very well known CEO of Informix, but we won't add more comments",0);
insert into persons values ("Rodrigues Ferreira Almeida Silva Ferreira Martins Nascimento","Antonio","Avenida da Republica 667-B","Lisboa","Antonio was a big fan of Informix, whose CEO obliged him to move to Oracle, but finally came back to Informix",0);
create index i_persons0 on persons(my_lower_varchar(City)); -- use a "varchar" function even for a char
create index i_persons1 on persons(my_lower_varchar(LastName)); -- use a "varchar" function for a varchar
create index i_persons2 on persons(my_lower_varchar(FirstName)); -- use a "varchar" function for a varchar with another size
create index i_persons3 on persons(my_lower_char(address)); -- use a "char" function for a varchar column: it works
create index i_persons4 on persons(my_lower_varchar(address)); -- use a "varchar" function for a varchar column: it works too
--create index i_persons5 on persons(my_lower_lvarchar(info)); -- I could not create the index on lvarchar, I have "too big index error" like message, which is due to limited size of index key (387B on 2kpages"
update statistics high for table persons;
set explain on;
select "OnLastName",* from persons where my_lower_varchar(LastName) = "sippl";
select "OnFirstName",* from persons where my_lower_varchar(FirstName) = "roger";
select "OnAddress-1",* from persons where my_lower_char(Address) = "Bohannon Drive 4100" ;
select "OnAddress-2",* from persons where my_lower_varchar(Address) = "Bohannon Drive 4100";
select "OnCity",* from persons where my_lower_varchar(city) = "menlo park";
select "OnInfo",* from persons where my_lower_lvarchar(info) matches "*ceo*";
set explain off;