title: Sorting SQL Server records by number of OR conditions matched
body: <p>Suppose I have a (code-generated) query that is looking for keywords in a number of different fields. For example, any one of the terms <code>University</code>, <code>South</code> or <code>Africa</code> might be in the name field, or the address field, or some other field. I want to find all records where any of these fields match any of these keywords:</p>
  
  <pre><code>SELECT *
  FROM
      Institutions
  WHERE
      Institutions.name LIKE '%University%' OR
      Institutions.address LIKE '%University%' OR
      Institutions.name LIKE '%South%' OR
      Institutions.address LIKE '%South%' OR
      Institutions.name LIKE '%Africa%' OR
      Institutions.address LIKE '%Africa%'
  </code></pre>
  
  <p>However, this is going to pull up <em>everything</em>. I would like to sort by the records that match the most possible conditions.</p>
  
  <p>I think this would work but looks pretty clunky:</p>
  
  <pre><code>SELECT *
  FROM
      Institutions
  WHERE
      Institutions.name LIKE '%University%' OR
      Institutions.address LIKE '%University%' OR
      Institutions.name LIKE '%South%' OR
      Institutions.address LIKE '%South%' OR
      Institutions.name LIKE '%Africa%' OR
      Institutions.address LIKE '%Africa%'
  ORDER BY
      (
          CASE WHEN Institutions.name LIKE '%University%' THEN 1 ELSE 0 END +
          CASE WHEN Institutions.address LIKE '%University%' THEN 1 ELSE 0 END +
          CASE WHEN Institutions.name LIKE '%South%' THEN 1 ELSE 0 END +
          CASE WHEN Institutions.address LIKE '%South%' THEN 1 ELSE 0 END +
          CASE WHEN Institutions.name LIKE '%Africa%' THEN 1 ELSE 0 END +
          CASE WHEN Institutions.address LIKE '%Africa%' THEN 1 ELSE 0 END
      ) DESC
  </code></pre>
  
  <p>Is there something cleaner? I'm fine with SQL Server-specific solutions but pure SQL solutions would be great.</p>