I'm trying to perform an specific term full-text search in SQL Server. It looks like the way to do it is by wrapping the keywords in double quotes, which I'm doing in my app:

Query = "\"" + model.SearchString + "\""

However, when I perform the query against the database with a term such as "foo bar", it returns every occurrence of "foo" and "bar" inside the database. The expected result is that the database should return only the records that have "foo bar" specifically. This is the query:

SELECT o.OpinionDocumentId
FROM CONTAINSTABLE(OpinionDocuments, Html, '"foo bar"') AS KEY_TBL
LEFT JOIN dbo.OpinionDocuments AS o ON o.OpinionDocumentId = KEY_TBL.[KEY]
WHERE o.Date BETWEEN @from AND @to

What could be the reason why this query is not returning the results from an specific set of keywords?



Your query actually needs to use two properties of the CONTAINSTABLE function-

<maximum distance> and <match_order>.


CONTAINSTABLE(OpinionDocuments, HTML, 'NEAR((Foo, bar), 0, TRUE)') AS KEY_TBL

Specifies to find "Foo" near "Bar" with 0 search objects between (<maximum distance> ", 0,") and in the order specified (<match_order> ",TRUE")

| improve this answer | |
  • I am not the OP, but I've got a question about your suggestion. Are you saying that wrapping a search, to CONTAINSTABLE in quotes doesn't actually perform an exact phrase lookup? What you're doing with NEAR is what I thought the quotes did, already. Is this not correct? Thanks! – Ryan Jan 21 '15 at 17:21
  • 1
    Yes, that's correct. The full-text indicies actually use "language tokens" (words, phrases) as a basis. For a brief overview of this, you can check out this article: simple-talk.com/sql/learn-sql-server/… – Thomas Cleberg Jan 21 '15 at 17:25

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.