>The 80-character string represents answers to qualification cards (like the ones you fill out when you subscribe to a magazine). If someone checks answer 2,5 and 7, the information is stored with 0s everywhere and 1s in positions 2, 5 and 7.
>
>The query is trying to address questions like: "Who checked answer 2 OR 5 OR 7?"
>
>I am moving this database up to SQL Server next, so I will have to find a solution that would work there also. Space is not too much of an issue, of course, and queries towards the FoxPro database (using SUBSTRING()) are lightning fast on a 200,000 record DB. I was just attempting a faster approach.
>
>I believe that:
>SELECT * ;
>FROM Table1 ;
>WHERE BITTEST(lnNum,1) ;
>OR BITTEST(lnNum,3)
>
>would be much faster than:
>SELECT * ;
>FROM Table1 ;
>WHERE SUBSTRING(lcString,1,1) ;
>AND SUBSTRING(lcString,3,1)
>
>I found out that 30 positions are always 0, so that leaves me with a 50-char string.
>----------------------------------------------------
>Here is another approach, but I cannot find the right functions to do it:
>a. I construct the string with 1s in the proper postions (say, 1,3,4) as 1011.
>b. I clear all bits except 1,3,4 in my String.
>c. I XOR the construced string with my String:
>"myString XOR 1011" =
.
>c. If the numeric equivalent of that binary number is greater than 0, I have a winner.
>
>How do I XOR two strings though? BITXOR accepts numeric values only.
If space isn't an issue, why not create a child table (1 to 1) of logical answers? Use the answer string to populate the table once and then you can run queries over and over. You could even build indices with common combinations of answers.
HTH
I try to stay online, to get better information.
I try to get away from the office once in a while..
But they KEEP... PULLING.. ME.. BACK.. IN!!!