4

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?

MKing
  • 518
  • 1
  • 4
  • 18
  • 1
    I know this is old, but I ran into the same issue and just found this bug report: [FREETEXT in SQL Server 2008 R2 no longer works as documented for "phrase searches"](http://connect.microsoft.com/SQLServer/feedback/details/683573/freetext-in-sql-server-2008-r2-no-longer-works-as-documented-for-phrase-searches) – bfavaretto Feb 10 '12 at 21:37

1 Answers1

4

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.

Lamak
  • 69,480
  • 12
  • 108
  • 116
  • 1
    So you're saying that when the documentation says, "a phrase match is instead performed" it doesn't mean (for lack of better terms) the exact phrase is matched? I guess I just find that to be misleading. Essentially there's no way to get exactly what i want, I'll need to parse the searchterm and submit quoted pieces as CONTAINS queries and non-quoted using FREETEXT? – MKing Dec 16 '10 at 16:54
  • You can use CONTAINS with or without quoted text, without them it behaves similar to FREETEXT. The difference is that whith FREETEXT it finds derivates of the words and not only the exact words. – Lamak Dec 16 '10 at 17:52