Re: Case INsensitive search

leblancs@tsagate.com (Sean LeBlanc) wrote: >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. > >TIA, >............................................................ When we querird Informix about that same problem, we were told that their 4GL tools could handle it. Unfortunately we were using PowerBuilder which could not. Their solution was to have duplicate columns (last_name, last_name_caps). Perform the search on the caps column, and read the data from the caps column. We implemented this and it worked no problem (except for table normalization). This is the only solution which was workable on our schedule, and we did not see a performance problem with it. A friend of mine used a Soundex algorithm (which Informix provided some literature on) which worked for them. -- // // stephen f. cawley //