FREETEXT queries in SQL Server 2008 not phrase matching

Question

I have a full text indexed table in SQL Server 2008 that I am trying to query for an exact phrase match using FULLTEXT. I don't believe using CONTAINS or LIKE is appropriate for this, because in other cases the query might not be exact (user doesn't surround phrase in double quotes) and in general I want to flexibility of FREETEXT.

According to the documentation[MSDN] for FREETEXT:

If freetext_string is enclosed in double quotation marks, a phrase match is instead performed; stemming and thesaurus are not performed.

which would lead me to believe a query like this:

SELECT Description  
FROM Projects   
WHERE FREETEXT(Description, '"City Hall"')  

would only return results where the term "City Hall" appears in the Description field, but instead I get results like this:

1 Design of handicap ramp at Manning Hall.
2 Antenna investigation. Client: City of Cranston Engineering Dept.
3 Structural investigation regarding fire damage to International Tennis Hall of Fame.
4 Investigation Roof investigation for proposed satellite design on Herald Hall.
... etc

Obviously those results include at least one of the words in my phrase, but not the phrase itself. What's worse, I had thought the results would be ranked but the two results I actually wanted (because they include the actual phrase) are buried.

SELECT Description  
FROM Projects  
WHERE Description LIKE '%City Hall%'  

1 Major exterior and interior renovation of the existing city hall for Quincy Massachusetts
2 Cursory structural investigation of Pawtucket City Hall tower plagued by leaks.

I'm sure this is a case of me not understanding the documentation, but is there a way to achieve what I'm looking for? Namely, to be able to pass in a search string without quotes and get exactly what I'm getting now or with quotes and get only that exact phrase?

Answer 1

As you said, FREETEXT looks up every word in your phrase, not the phrase as an all. For that you need to use the CONTAINS statement. Like this:

SELECT Description  
FROM Projects   
WHERE CONTAINS(Description, '"City Hall"')

If you want to get the rank of the results, you have to use CONTAINSTABLE. It works roughly the same, but it returns a table with two columns: [Key] wich contains the primary key of the search table and [Rank], which gives you the rank of the result.