Re: Case Insenstive Matching

INFORMIX TAKE NOTE, AGAIN: Here's yet another request for a case-insensitive search facility in SQL. This is a *very* FAQ and it bugs the hell out of me that there is no good answer to it - it CAN'T be hard to provide (even if the performance of such querys might be appalling) Hell, you're parading about with this Illustra deal telling us how we'll be able to search *BLOBS* with syntaxes like (and I'm making this up) SELECT picture FROM nice_scenes WHERE picture INCLUDES "pretty clouds" AND picture INCLUDES "small boat" or SELECT sound FROM famous_speeches WHERE sound INCLUDES "I have a dream" OR sound INCLUDES "One small step" but we still can't say SELECT free_form_text FROM famous_speeches WHERE UPPERCASE(free_form_text) MATCHES "*WE WILL FIGHT THEM ON THE BEACHES*" Sheesh. There are some wonky priorities going on somewhere! Love 'n Kisses, Kerry S (venting some frustrations into the ether, just in-case somebody listens :-) PS: If this is scheduled for inclusion sometime, would somebody mind dropping me a note and telling me - then I could shut up and leave everybody alone. -------------------------------------------------------------------- In article <4djagg$ks8@sleepy.inch.com>, elharo@metric.inch.com (Elliotte Rusty Harold) wrote: >Is there a simple way to do a case insensitive search in Informix /ESQLC? >I'd like to be able to search for *chimp* for example, and come up with >all matches that include nboth chimp and Chimp and CHIMP. The two ways >I've determined so far, neither of which I like: > >1. Convert my search into *[Cc]{Hh][Ii][Mm][Pp]* which seems like an >awful lot of work for such a common request > >2. Add an extra column to my database that contains the search field in >all upper case. I woulfd then search this column but return the original >column. This would not be the most relational way to design a database >(although my data is realtively flat for my simple application.) > >There isn't an uppercase function hiding anywhere that would let me >do a > >select * from photos where ucase(caption) matches *CHIMP*> >is there? If there is could you please point me at the appropriate >section of the Informix reference manual. Email responses much appreciated. > >-- >Elliotte Rusty Harold >elharo@shock.njit.edu >elliotte@blackstar.com > -- ------------------------------------,------------------------------------------ Kerry Sainsbury, kerry@kcbbs.gen.nz | THE INFORMIX FAQ v2.4 Oct 95 Quanta Systems, Auckland | kcbbs.gen.nz:/pub/informix/ New Zealand. Work: +64 9 377-4473 | mathcs.emory.edu:/pub/informix/faq/ Home: +64 9 279-3571 | http://www.garpac.com/informix.html