Re: Help with select/between
I havent seen a proper answer to this problem: > In article <441ek8$r7s@cssun.mathcs.emory.edu>, > Ward Kaatz <wardk@fourgen.com> wrote: > >At 02:31 9.22.95 GMT, Tammy Croteau wrote: > >}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*" > > > >AVSA is not *between* "A*" and "AV*' > > > >--> 'AVT*' or 'AW*' would pick up 'AVSA' > > > john@sashimi.wwa.com (pinoy_ako) writes: > What about this sql statement? > > select <field> from <table> where <field> matches "A*" > union > select <field> from <table> where <field> matches "AV*" This will obviously give the wrong answer as it will include every record that mathces A* To Waard Kaatz between isn't matches so what you get from your select is literally what is between A* and AV* where the * is taken as any regular character. What you can do is remove the * in both parts and extend the last part with the highest character allowed in your data. If you allow only characters A to Z it would become: select <field_name> from <table> where <field_name> between "A" and "AVZZ" If you also accept 8 bit characters the ZZ would have to be the highest possible 8 bit character value. You can allow the user to key any number of characters for the value before and after the "and" in this statement. The last part you simply extend with your "high value"-character to the length of the field (seemingly 4 characters here) while you do nothing to the part before the and. If you want to do this as part of a construct statement in 4GL you will have to do some fancy reformatting of whatever is keyed in. If the user types * in a field a matches will be generated. You will have to analyze this, replacing the matches with between and whatever the user typed with the above construct. It will be much easier to do this via a regular input statement and build the where part yourself. Matches with * or ? isn't very usefull for your purposes. Nils.Myklebust@ccmail.telemax.no NM-data, Dalsbergstien 7, N-0170 Oslo, Norway My opinions are those of my company