Fulltext search multiple columns for multiple search terms
<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>