Re: Case INsensitive search

> leblancs@tsagate.com (Sean LeBlanc) writes: > We are using Informix as one of our database backends for a product of ours. > Practically all other db's support the function "upper" in embedded SQL, which > allows us to do a case insensitive search, but Informix seems to be lacking > this functionality. (*NOTE*: I called Informix tech support for Dynamic > Server, and they were not too helpful) > What I want to be able to do is, given criteria like "jones", I want to be > able to find stuff like "Jones", "JONES", "JoNeS", etc. > > If anyone know a workaround that is somewhat sane, without building a huge > IN clause or the like, please enlighten me. I could use some enlightenment. :) One way of doing it is to add another field to your tables (or several fields). Whenever you insert a record you insert the upper-case version of the field you want to search on. Later when you search you use this field. This is heavy if all you want is what you describe. We put a phonetic code in this extra field so we get full phonetic search instead. In this way we can also use a shorter field than the original. Of course we also index this field so searches are fast. (Your "upper" function in sql would also have to be implemented somehow in the indexing system of the database to avoide sequential searches when you use it.) If this phonetic algorithm could have been implemented in Informix SPL it would have helped us a lot. The current language is to weak for that, so it must be in every application that does updates/inserts - very bad. When, dear Informix, will you see the light, and give us a better SPL? The NewEra language plus call to dynamically linked libraries written in other languages like C/C++ is what we are looking for. Nils.Myklebust@ccmail.telemax.no NM-data, Dalsbergstien 7, N-0170 Oslo, Norway My opinions are those of my company