Re: Doing case-insensitive searches

Art S. Kagel wrote: > Lisa Retief wrote: > > > > We have just discovered that Informix will only do case-sensitive searches, > > which is a huge problem for a project we are working on. It is possible to > > do a match using syntax such as [A][a] etc, but then the table indexing is > > of no use to us and such searches are far too expensive. Has anyone come > > across a solution for this dilemma? Is there a way to set an Informix > > database to be case-insensitive. > > > > I would appreciate any input. Please respond via email where posible. > > Version 7.30+ can do case insensitive searches and 8.30+ can build case > insensitive indexes as can 9.xx+. If you cannot upgrade do the old > store the data in all upper or lower case in an additional column or > a lookup table. Actually, 7.30 doesn't do case-insensitive searches. It has built-in UPPER() and LOWER() functions, but this is not the same thing as case-insensitive. The same indexing / performance issues that Lisa mentions would still be a factor using UPPER() or LOWER(). As mentioned, unless you are on 8.3 (?) or IUS (sorry, IDS/UDO or whatever it's called now) , you're best off adding an all-uppercase column. June -- june_t@hotmail.com Grounded in Palo Alto, living on M&M's (plain)