2

I don't understand why I'm getting these results with my full-text search query in SQL Server 2014.

The following query returns results with "Supervisor" in the Title field and "Tokyo" in the HTML_Description field.

SELECT 
    * 
FROM post
    JOIN CONTAINSTABLE([post], (Title, HTML_Description), 'Supervisor AND NOT Tokyo') 
        AS tb1 ON tb1.[Key] = post.ID

If I remove the HTML_Description field in the CONTAINSTABLE like this:

SELECT 
    * 
FROM post 
    JOIN CONTAINSTABLE(post, (Title), 'Supervisor AND NOT Tokyo') 
        AS tb1 ON tb1.[Key] = post.ID

The result is the same.

If I use only the HTML_Description field in the CONTAINSTABLE like this:

SELECT 
    * 
FROM post 
    JOIN CONTAINSTABLE(post, (HTML_Description), 'Supervisor AND NOT Tokyo') 
        AS tb1 ON tb1.[Key] = post.ID

There aren't any results.

How can I apply the NOT operator to all fields?

1

I'm not super familiar with full text searches but I think this StackOverflow answer gives us the answer which is also supported by this StackOverflow answer. The solution was to create a computed column containing what is essentially a concatenation of all the columns you want to apply your search terms to and do your full text search on that column.

Alternatively you can use the AND, OR, NOT logic in separate expressions like the OP in the StackOverflow answer I linked to.

That being said someone here might have a better solution.

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.