title: PL/SQL Comparing bit strings, I need an AND operation?
body: <p>I currently have a script that calculates the tanimoto coefficient on the fingerprints of a chemical library. However during testing I found my implementation could not be feasibly scaled up due to my method of comparing the bit strings (It just takes far too long). See below. This is the loop I need to improve. I've simplified this so it is just looking at two structures the real script does permutations about the dataset of structures, but that would over complicate the issue I have here.</p>
  
  <pre><code>LOOP
  -- Find the NA bit
  SELECT SUBSTR(qsar_kb.fingerprint.fingerprint, var_fragment_id ,1) INTO var_na FROM qsar_kb.fingerprint where project_id = 1 AND structure_id = 1;
  
  -- FIND the NB bit
  SELECT SUBSTR(qsar_kb.fingerprint.fingerprint, var_fragment_id ,1) INTO var_nb FROM qsar_kb.fingerprint where project_id = 1 AND structure_id = 2;
  
  -- Test for both bits the same
  IF var_na &gt; 0 AND var_nb &gt; 0 then
  var_tally := var_tally + 1;
  END IF;
  
  -- Test for bit in A on and B off
  IF var_na &gt; var_nb then
  var_tna := var_tna + 1;
  END IF
  
  -- Test for bit in B on and A off.
  IF var_nb &gt; var_na then
  var_tnb := var_tnb + 1;
  END IF;
  
  var_fragment_id := var_fragment_id + 1;
  EXIT WHEN var_fragment_id &gt; var_maxfragment_id;
  END LOOP;
  </code></pre>
  
  <p>For a simple example</p>
  
  <p>Structure A = '101010'</p>
  
  <p>Structure B = '011001'</p>
  
  <p>In my real data set the length of the binary is 500 bits and up. 
  I need to know:</p>
  
  <p>1)The number of bits ON common to Both</p>
  
  <p>2)The number of bits ON in A but off in B</p>
  
  <p>3)The number of bits ON in B but off in B</p>
  
  <p>So in this case</p>
  
  <p>1) = 1</p>
  
  <p>2) = 2</p>
  
  <p>3) = 2</p>
  
  <p>Ideally I want to change how I'm doing this. I don't want to be steeping though each bit in each string its just too time expensive when I scale the whole system up with thousands of structures each with fingerprint bit strings in the length order of 500-1000</p>
  
  <p>My logic to fix this would be to:</p>
  
  <p>Take the total number of bits ON in both</p>
  
  <p>A) = 3</p>
  
  <p>B) = 3</p>
  
  <p>Then perform an AND operation and find how many bits are on in both
  = 1 </p>
  
  <p>Then just subtract this from the totals to find the number of bits on in one but not the other.</p>
  
  <p>So how can I perform an AND like operation on two strings of 0's and 1's to find the number of common 1's?</p>