case insensitive search

You can do 2 things. The appropraite solution would be based on the frequency and size of the table.

The simplest is to convert both to upper case:

select * from table1 where upper(a1) like "%FOO%"

This will cause the entire table to be scanned and an index not to be used. If your table is large or the number of searches is large, then you should create an additional column in your table that contains the upper case of the field you want to search (upper_a1 in your example). If you create an index on your column, you have a better chance (no quarantees though) that the index might be used, though a like with a leading and trailing % is never good (efficient).

You code would then look more like:

select * from table1 where upper_of_a1 like "%FOO%"

Make sense?

David

Hi David,

thanks for the fast response, but it looks like informix does not have an upper() function :(
Also, your second idea sounds good, but it would consume too much space.

thx

maf

I found the following on Deja news. Hope it helps.

If you have 7.3x or later you can use the upper or lower function and store names as all lower case or upper case, thus:
 
select * from customers where last_name_upper = UPPER( :input );
 
This will even use an index on last_name_upper.  If you do not want to store the upper case version of the name you can use upper on both the input and the column but it will not use indexes:
 
select * from customers where UPPER( last_name ) = UPPER( "input );
 
If you do not have 7.30+ there is a stored procedure version of UPPER() in the IIUG Repository but it is SLOW or you could store upper and convert the input to upper case in code.