title: SQL Server : search 2 tables using a LIKE opertaor
body: <p>I have a SQL Server table <code>Cases</code> with a column that contains casenumbers and a column called <code>match</code>. </p>
  
  <p>I also have another table <code>Messages</code>. In this table there is one column called <code>Message</code> that contains large amounts of random text strings that may or may not contain the case number noted in the <code>Case</code> table. </p>
  
  <p>What I want to do is loop through every single record in table <code>Cases</code> and see if each of those case numbers exist anywhere within a record in the message column in table <code>Messages</code>. </p>
  
  <p>I need to use the LIKE operator which includes the case number from <code>Cases</code> (casenumbers). If a case number is found anywhere in table <code>Messages</code>, I want to update <code>Cases.Match</code> column to 1 (true).</p>
  
  <p>I am lost as how to accomplish this. So far I am just trying to get casenumbers that exist somewhere in the <code>Message</code> column in table <code>Messages</code>. </p>
  
  <pre><code>SELECT *
  FROM Cases
  INNER JOIN Messages ON Cases.CaseNumber LIKE '%'+ Messages.Message + '%'
  </code></pre>
  
  <p>Data structures</p>
  
  <p><strong>Cases</strong>:</p>
  
  <ul>
  <li>Casenumber VARCHAR(MAX)</li>
  <li>Match BIT</li>
  </ul>
  
  <p><strong>Messages</strong>:</p>
  
  <ul>
  <li>Message VARCHAR(MAX)</li>
  </ul>
  
  <p>I am using SQL Server 2008 R2</p>