title: SQL Server searching for text in a column
body: <p>I'm confused over what to use?</p>
  
  <p>Basically I need to have a search string that can search a single column for the occurrences of multiple phrases, each input phrase is separated by a space.</p>
  
  <p><strong>So input from user would be like:</strong></p>
  
  <pre><code>"Phrase1 Phrase2 ... PhraseX"     (number of phrases can 0 to unknown!, but say &lt; 6)
  </code></pre>
  
  <p><strong>I need to search with the logic:</strong></p>
  
  <pre><code>Where 'Phrase1%' **AND** 'Phrase2%' **AND** ... 'PhraseX%'
  </code></pre>
  
  <p>.. etc... so all phrases need to be found.</p>
  
  <p>Always logical AND</p>
  
  <p>SO speed, performance taken in to account, <strong>Do I use:</strong></p>
  
  <p>Lot's of </p>
  
  <pre><code>Like 'Phrase1%' and like 'Phrase2%' and like ... 'PhraseX%' ?
  </code></pre>
  
  <p><strong>or use</strong></p>
  
  <pre><code>patindex('Phrase1%' , column) &gt; 0 AND  patindex('Phrase2%' , column) &gt; 0 
  AND ...     patindex('PhraseX%' , column) 
  </code></pre>
  
  <p><strong>or use</strong></p>
  
  <p>add a Full Text search Index,</p>
  
  <p>The use:</p>
  
  <pre><code>Where Contatins(Column, 'Phrase1*') AND Contatins(Column, 'Phrase2*') AND ... Contatins(Column, 'PhraseX*')
  </code></pre>
  
  <p><strong>Or</strong> </p>
  
  <p>???? </p>
  
  <p>Almost too many options, which is why I'm asking, what would be the most efficient way of doing this...</p>
  
  <p>Your wisdom is appreciated...</p>