Re: Case insensitive search

Posted in 1997

Art S. Kagel — Fri, 25 Jul 1997 10:46:44 -0400 — source: Informix-list mailing list archive (1991-1998)

Jay D. Sullivan wrote: > > Hi. > > We have Informix OWS 7.22 for NT and I'm wondering if there is a way to do > case-insensitive searches. > > It looks like Informix does not have a native UPPER() function. Last year > tech support sent me an UPPER stored procedure which basically parses a > string and changes 'a' to 'A', 'b' to 'B' and so on. With this procedure, > I can run a query like "select custname from customer where upper(custname) > like 'CON%'". The problem with this method is that Informix won't/can't > use the index on the custname field. So, the query can be *really* slow if > the table is large. > > Is there another method which I'm overlooking? Can OWS or ODS be > configured to be case-insensitive? No. The standard method is to have a copy of the name in all upper or all lower case either in the table proper or as a separate lookup table ala star schemas. Art S. Kagel