Sql-server – Problem with “not” in full text search

full-text-searchsql serversql server 2014

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?

Best Answer

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.

Related Question