title: SQL: Find rows where Column contains all of the given words
body: <p>I have some column EntityName, and I want to have users to be able to search names by entering words separated by space. The space is implicitly considered as an 'AND' operator, meaning that the returned rows must have all of the words specified, and not necessarily in the given order.</p>
  
  <p>For example, if we have rows like these:</p>
  
  <ol>
  <li>abba nina pretty balerina</li>
  <li>acdc you shook me all night long</li>
  <li>sth you are me</li>
  <li>dream theater it's all about you</li>
  </ol>
  
  <p>when the user enters: <code>me you</code>, or <code>you me</code> (the results must be equivalent), the result has rows 2 and 3.</p>
  
  <p>I know I can go like:</p>
  
  <pre><code>WHERE Col1 LIKE '%' + word1 + '%'
    AND Col1 LIKE '%' + word2 + '%'
  </code></pre>
  
  <p>but I wanted to know if there's some more optimal solution.</p>
  
  <p>The <code>CONTAINS</code> would require a full text index, which (for various reasons) is not an option.</p>
  
  <p>Maybe Sql2008 has some built-in, semi-hidden solution for these cases?</p>