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.
Aristotle