title: How do you use T-SQL Full-Text Search to get results like Google?
body: <h1>What I Need</h1>
  
  <p>I have a database with fields that can contain long phrases of words. I wanted the ability to quickly search for a keyword or phrase in these columns, but when searching a phrase, I want to be able to search the phrase like Google would, returning all rows that contain all of the specified words, but in no particular order or "nearness" to each other. Ranking the results by relevance is unnecessary at this point.</p>
  
  <p>After reading about SQL Server's <a href="http://msdn.microsoft.com/en-us/library/ms142571.aspx" rel="noreferrer">Full-Text Search</a>, I thought it would be just what I needed: a searchable index based on each word in a text-based column. My end goal is to safely accept user input and turn it into a query that leverages the speed of Full-Text Search, while maintaining ease-of-use for the users.</p>
  
  <h1>The Problem: Full-Text Search functions don't search like Google</h1>
  
  <p>I see the <a href="http://msdn.microsoft.com/en-us/library/ms142583.aspx#OV_ft_predicates" rel="noreferrer"><code>FREETEXT</code> function</a> can take an entire phrase, break it up into "useful" words (ignoring words like 'and', 'or', 'the', etc), and then return a list of matching rows very quickly, even with a complex search term. But when you try to use it, you may notice that instead of an <code>AND</code> search for each of the terms, it seems to only do an <code>OR</code> search. Maybe there's a way to change its behavior, but I haven't found anything useful.</p>
  
  <p>Then there's <a href="http://msdn.microsoft.com/en-us/library/ms142583.aspx#OV_ft_predicates" rel="noreferrer"><code>CONTAINS</code></a>, which can accept a boolean query phrase, but sometimes with odd results.</p>
  
  <p>Take a look at the following queries on this table:</p>
  
  <h2>Data</h2>
  
  <pre><code>PKID    Name
  -----   -----
  1       James Kirk
  2       James Cameron
  3       Kirk Cameron
  4       Kirk For Cameron
  </code></pre>
  
  <h2>Queries</h2>
  
  
  
  <pre class="lang-sql prettyprint-override"><code>Q1: SELECT Name FROM tblName WHERE FREETEXT(Name, 'james')
  Q2: SELECT Name FROM tblName WHERE FREETEXT(Name, 'james kirk')
  Q3: SELECT Name FROM tblName WHERE FREETEXT(Name, 'kirk for cameron')
  Q4: SELECT Name FROM tblName WHERE CONTAINS(Name, 'james')
  Q5: SELECT Name FROM tblName WHERE CONTAINS(Name, '"james kirk"')
  Q6: SELECT Name FROM tblName WHERE CONTAINS(Name, '"kirk james"')
  Q7: SELECT Name FROM tblName WHERE CONTAINS(Name, 'james AND kirk')
  Q8: SELECT Name FROM tblName WHERE CONTAINS(Name, 'kirk AND for AND cameron')
  </code></pre>
  
  <h2>Query 1:</h2>
  
  <pre class="lang-sql prettyprint-override"><code>SELECT Name FROM tblName WHERE FREETEXT(Name, 'james')
  </code></pre>
  
  <p>Returns "James Kirk" and "James Cameron". Alright, lets narrow it down...</p>
  
  <h2>Query 2:</h2>
  
  <pre class="lang-sql prettyprint-override"><code>SELECT Name FROM tblName WHERE FREETEXT(Name, 'james kirk')
  </code></pre>
  
  <p>Guess what. Now you'll get "James Kirk", "James Cameron", and "Kirk For Cameron". Same thing happens for <strong>Query 3</strong>, so let's just skip that.</p>
  
  <h2>Query 4:</h2>
  
  <pre class="lang-sql prettyprint-override"><code>SELECT Name FROM tblName WHERE CONTAINS(Name, 'james')
  </code></pre>
  
  <p>Same results as Query 1. Okay. Narrow the results maybe...?</p>
  
  <h2>Query 5:</h2>
  
  <pre class="lang-sql prettyprint-override"><code>SELECT Name FROM tblName WHERE CONTAINS(Name, '"james kirk"')
  </code></pre>
  
  <p>After discovering that you need to enclose the string in double-quotes if there are spaces, I find that this query works great on this particular dataset for the results I desire! Only "James Kirk" is returned. Wonderful! Or is it...</p>
  
  <h2>Query 6:</h2>
  
  <pre class="lang-sql prettyprint-override"><code>SELECT Name FROM tblName WHERE CONTAINS(Name, '"kirk james"')
  </code></pre>
  
  <p>Crap. No. It is matching that exact phrase. Hmmm... After checking the <a href="http://msdn.microsoft.com/en-us/library/ms187787.aspx" rel="noreferrer">syntax for T-SQL's CONTAINS function</a>, I see that you can throw boolean keywords in there, and it looks like that might be the answer. Let's see...</p>
  
  <h2>Query 7:</h2>
  
  <pre class="lang-sql prettyprint-override"><code>SELECT Name FROM tblName WHERE CONTAINS(Name, 'james AND kirk')
  </code></pre>
  
  <p>Neat. I get all three results, as expected. Now I just write a function to cram the word <code>AND</code> between all the words. Done, right? What now...</p>
  
  <h2>Query 8:</h2>
  
  <pre class="lang-sql prettyprint-override"><code>SELECT Name FROM tblName WHERE CONTAINS(Name, 'kirk AND for AND cameron')
  </code></pre>
  
  <p>This query knows exactly what it's looking for, except for some reason, there are no results. Why? Well after reading about <a href="http://msdn.microsoft.com/en-us/library/ms142551.aspx" rel="noreferrer">Stopwords and Stoplists</a>, I will make an educated guess and say that because I'm asking for the intersection of the index results for "kirk", "for", and "cameron", and the word "for" will not have any results (what with it being a stopword and all), then the result of any intersection with that result is also empty. Whether or not it actually functions like that is irrelevant to me, since that is the observable behavior of the <code>CONTAINS</code> function every time I do a boolean search with a stopword in there.</p>
  
  <p>So I need a new solution.</p>
  
  <h1>Here comes <a href="http://msdn.microsoft.com/en-us/library/ms142568.aspx" rel="noreferrer"><code>NEAR</code></a></h1>
  
  <p>Looks promising. If I can take a user query and put commas between it, this will... wait this is the same thing as using boolean <code>AND</code> in <code>CONTAINS</code> queries. But does it ignore stopwords correctly?</p>
  
  <pre class="lang-sql prettyprint-override"><code>SELECT Name FROM tblName WHERE CONTAINS(Name, 'NEAR(kirk, for, cameron)')
  </code></pre>
  
  <p>Nope. No results. Remove the word "for", and you get all three results again. :(</p>
  
  <h1>What now?</h1>