CODEWITHSUNDEEP
sql-serverfull-text-search
lecuong92
lecuong92

Reputation: 43

I dont' understand with “not” in full text search

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?

Upvotes: 2

Views: 227

Answers (1)

Keith
Keith

Reputation: 21264

Why does this happen? Because even when searching across multiple columns, the full text search conditions must be satisfied within a single column. Thus your query is equivalent to:

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

Ways to work around this:

You should combine Title and HTML_Description into a single column (you can even create a computed column to do this) and create the full text index on this column.

Worth mentioning: In most cases you can rewrite the query using 2 or more CONTAINSTABLE or CONTAINS statements to search each keyword individually -- CONTAINS(*, 'apples') and CONTAINS(*, 'oranges') instead of CONTAINS(*, 'apples AND oranges') -- but then you'll either get multiple meaningless Rank columns (because each is calculated against a single keyword) or you won't get a Rank at all (if you use CONTAINS). The link below shows a simple example of this. However this doesn't apply to your case because of your use of NOT. You cannot perform a full text search using only a NOT condition -- CONTAINS(*, 'NOT Tokyo') will throw an error.

See Full-Text Search Queries with CONTAINS Clause Search Across Columns

Upvotes: 2

Related Questions