Re: Doing case-insensitive searches

Oliver Wardman wrote: > > 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. > > > > Art S. Kagel > > Can someone tell me how to carry out case insensitive searches as > described above? I'm on IDS 7.30.UC3 Try: SELECT my_char_col FROM my_table WHERE LOWER(my_char_col) = "my_search_string_in_lower_case" Cheers, -- Mark. +----------------------------------------------------------+-----------+ |Mark D. Stock - Informix SA http://www.informix.com |//////// /| |mailto:mdstock@informix.com http://www.informix.com/idn |///// / //| |http://www.iiug.org +-----------------------------------+//// / ///| | Tel: +27 11 807 0313 |If it's slow, the users complain. |/// / ////| | Fax: +27 11 807 2594 |If it's fast, the users keep quiet.|// / /////| |Cell: +27 83 250 2325 |Therefore, "No news: travels fast"!|/ ////////| +----------------------+-----------------------------------+-----------+