title: Fulltext search multiple columns for multiple search terms
body: <p>I have a requirement from a client to have a search-field where he wants to input any text and search for every word in that text field in multiple full-text indexed columns which contain customer information, from a customer information table.</p>
  
  <p>So, for example, if he inputs <code>FL Diana Brooks Miami 90210</code>, he wants all of these terms (<code>FL</code>, <code>Diana</code>, <code>Brooks</code>, <code>Miami</code>, <code>90210</code>) to <strong>each be searched</strong> into the State, FirstName, LastName, City and Zip columns.</p>
  
  <p>Now, this seems totally a bad idea to begin with and as an alternative I suggested using multiple fields where to input this information separately. Nonetheless, the point I am at is <strong>having to</strong> make a proof of concept as to why this won't work, from a performance perspective, and that it's better to have multiple fields where you input the term you want to search for.</p>
  
  <p>So, getting to my query, I'm trying to write a Full-Text query to do what the client has asked for in order to get a benchmark for performance.</p>
  
  <p>What I have so far doesn't seem to work, so I guess <strong><em>I am asking if it's even possible to do this?</em></strong></p>
  
  <pre><code>declare 
      @zip varchar(10)            = 90210
      , @lastName varchar(50)     = 'Brooks'
      , @firstName varchar(50)    = 'Diana'
      , @city varchar(50)         = 'Miami'
      , @state char(2)            = 'FL'
      , @searchTerm varchar(250)  = ''
      , @s varchar(1) = ' '
  
  set @searchTerm = @state + ' ' + @firstName + ' ' + @lastName + ' ' + @city
  
  select * 
  from freetexttable(contacts, (zip, lastName, FirstName, city, state), @searchTerm) ftTbl
  inner join contacts c on ftTbl.[key] = c.ContactID
  </code></pre>
  
  <p>The query I have above seems to work, but is not restrictive enough in order to find only the single record I'm looking for and is returning a whole lot more (which I'm guessing that it's because I'm using <code>FREETEXTTABLE</code>).</p>
  
  <p>I've also tried replacing it with <code>CONTAINSTABLE</code>, but I get an error saying:</p>
  
  <blockquote>
    <p>Msg 7630, Level 15, State 3, Line 26 </p>
    
    <p>Syntax error near 'Diana' in the full-text search condition 'FL Diana Brooks Miami'.</p>
  </blockquote>
  
  <p>With using regular indexes I have been able to solve this, but I'm curious if it's even possible to do the same thing with Full-Text.</p>
  
  <p>Using regular indexes I have a query with a adaptable WHERE clause, like below:</p>
  
  <pre><code>WHERE C.FirstName like coalesce(@FirstName + '%' , C.FirstName)
      AND C.LastName like coalesce(@LastName + '%' , C.LastName)
      etc.
  </code></pre>