Re: Help with select/between

Spokey the Wheeler (spokey@haunted.demon.co.uk) wrote: : In article: <43uhcu$4k7@ftcnews.nrcs.usda.gov> tammy@scooter (Tammy Croteau) writes: : > : > In a table I have the following data: : > AGCR : > AGST : > ALAR : > ANGE : > AVSA : > AXXA : > BRAM : > : > The user wants to wildcard select to get different variations of : > data. If they enter in A*-AV* they expect to get the first 5 items : > on the list. : > : > My sql statement is as follows: : > select <field_name> from <table> where <field_name> between "A*" and "AV*" : > : > The problem is that all data up to (but not including) AV* is returned. The : > only way I have found to get the AV* data is to include a matches AV* clause : > on the end of the statement. : > : > Is there a better way?????? Any help is much appreciated! I don't think BETWEEN is treating the wildcard as you think it is, but instead is treating it as a single character ASCII value (052), so the value "AVSA" is outside of "AV*" (S being greater than * in and ASCII value sense). The asterisk is treated as a wildcard only if it is used in conjunction with MATCHES. You need to break the request into something like: ... WHERE <field_name> BETWEEN "A" AND "AV~~~~~~~" (tilde being the last ASCII character in the visible spectrum; I suspect "AVzzzzzzzzz" would suffice for most). You won't get CONSTRUCT to do this for you automatically, mixing wildcards with other query operators is dicey as best (I don't think A*-AV* or A*:AV* or even A:AV* would give you the desired results) so either educate your users to put in "A:AVzzzzzzzzzz" in the CONSTRUCT (this *would* result in the desired set, I'll bet), or write some 4GL code (less than complex, although more than trivial) to handle it. Let me know if I should elaborate on the 4GL method (and Walt will tell you, this kid can *elaborate*). ======================================================================= Dennis J. Pimple dennisp@informix.com Opinions expressed Senior Consultant -------------------- are mine, and do not Informix Software Inc Voice: 303-850-0210 necessarily reflect Denver Colorado USA Fax: 303-779-4025 those of my employer.