title: Dropping noise words in SQL Server 2005 full text indexing
body: <p>In a pretty typical scenario, I have a 'Search' text box on my web application which has user input passed directly to a stored procedure which then uses full text indexing to search on two fields in two tables, which are joined using appropriate keys.</p>
  
  <p>I am using the CONTAINS predicate to search the fields. Before passing the search string in, I do the following:</p>
  
  <pre><code>SET @ftQuery = '"' + REPLACE(@query,' ', '*" OR "') + '*"'
  </code></pre>
  
  <p>Changing <em>the castle</em> to <em>"the*" OR "castle*"</em>, for example. This is necessary because I want people to be able to search on <em>cas</em> and get results for <em>castle</em>.</p>
  
  <pre><code>WHERE CONTAINS(Building.Name, @ftQuery) OR CONTAINS(Road.Name, @ftQuery)
  </code></pre>
  
  <p>The problem is that now that I have appended a wildcard to the end of each word, noise words (e.g. <em>the</em>) also have a wildcard appended and therefore no longer appear to get dropped. This means that a search for <em>the castle</em> will return items with words such as <em>theatre</em> etc.</p>
  
  <p>Changing OR to AND was my first thought, but that appears to simply return no matches if a noise word is then used in the query.</p>
  
  <p>All I am trying to achieve is to allow the user to enter multiple, space separated words that respresent either the entirety or a prefix of the words they are searching on, in any order - and drop noise words such as <em>the</em> from their input (otherwise when they search for <em>the castle</em> they get a big list of items with the result they need somewhere in the middle of the list.</p>
  
  <p>I could go ahead and implement my own noise word removal procedure, but it seems like something that full text indexing ought to be able to handle.</p>
  
  <p>Grateful for any help!</p>
  
  <p>Jamie</p>