title: Case statement in left join
body: <p>So I have two tables, which both have a <code>cocnumber</code> or ChamberOfCommerce-number. This is always a 8-digit number, so e.g. <code>12345678</code>. There is a problem though: the first table <code>aa</code> has the correct COC numbers of 8-digits, but the second table <code>bb</code> has a mix of 11 or 12-digit COC numbers. 11 AND 12-digit numbers have four 0's at the end of the number, which are irrelevant. </p>
  
  <p>Example 11-digit COC:
  <code>12345670000</code></p>
  
  <p>Example 12-digit COC: 
  <code>123456780000</code></p>
  
  <p>But why are there 11-digit COC numbers? Well, there aren't. What happend is that 11-digit COC numbers should have a <code>0</code> in front of them: <code>0123456780000</code>, which got cut off when it was stored in the database.</p>
  
  <p>So in the case of a 12-digit COC number, I just cut off the last 4 digits. But for a 11-digit COC number, I have to cut off the last 4 digits AND add a 0 in front of the COC number.</p>
  
  <p>My question is how I would make a CASE statement that performs this manipulation, inside the LEFT JOIN?</p>
  
  <p>This is what I have:</p>
  
  <pre><code>SELECT
    aa.cocnumber,
    bb.emloyeesnrtotal AS bb_emloyeesnrtotal
  
  FROM aa
    LEFT JOIN bb ON aa.cocnumber = 
              CASE 
                  WHEN LEN(bb.cocnumber) = 12
                      THEN LEFT(bb.cocnumber, 8)
                  WHEN LEN(bb.cocnumber) = 11
                      THEN '0' + LEFT(bb.cocnumber, 7)
                  END
  </code></pre>
  
  <p>This doesn't seem to produce the correct result though. Can anyone help me point to what I'm doing wrong? I think I'm taking the length of the entire <code>bb.cocnumber</code> column here, but how do I make a statement that evaluates each cell of <code>bb.cocnumber</code> individually to see if there is a matching 8-digit <code>aa.cocnumber</code>?</p>