Case INsensitive search

> > 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. :) > I'm thinking that one way to do this, with a correspondinging speed advantage to boot, is to have a little trigger which updates a column on the table after every insert/update. Its purpose would be to turn every such character key into an integer - for case sensitivity you upshift first. Then when you want a specific value, just use the same algorithm on the character value and then read using the integer key. The only thing I don't know off the top of my head is whether you can upshift in an SPL. > Quote o' the moment: > "You can't fight in here, this is the WAR room!" cute. cheers j. _____________________________________________________________________________ Jack Parker - Hewlett Packard, DMD/IS Boise, Idaho, USA jparker@hpbs3645.boi.hp.com _____________________________________________________________________________ Lysdexics Untie! _____________________________________________________________________________ Any opinions expressed herein are my own and not those of my employers. _____________________________________________________________________________