Re: Workgroup server case insensetive searching

Hans van der Pol <hans@netscape.com> wrote: :Hi : :Does anyone know how to search case-in-sensetive with the Informix :on-line workgroup server? It seems that there is no uppercase function :or what so ever (like in Oracle: Upper()). The only option I can think :of, is upgrading to another database (Informix or others). : :Please help, Check the faq (see my signature) for some methods. As allways the best method is to create an extra column in the table, store the upper case version of the data in that and use it for searching. That has the added benefit that it can easily be indexed for fast searching. Even though Oracle have an upper function that can be used in a where clause it's reported one can't create an index using this function. With any significant amount of data to search through that would mean it would be very slow. Informix have said something about adding an upper function in the 7.3 version of the engine. I don't know if that will include beeing able to create indexes using that function. In IUS you can do both (although you may have to write your own upper function, something that should be reasonably easy if it isn't allready available from someone). With IUS you could also easily implement a metaphone (or soundex) search with an index. Using the routines for this purpose available at the IIUG site you can also do that with an adittional column in the current engine. Such searches often give much better result than a simple upper function do. Nils.Myklebust@idg.no NM Data AS, P.O.Box 9090 Gronland, N-0133 Oslo, Norway My opinions are those of my company The Informix FAQ is at http://www.iiug.org