SQL Full Text Search NOT contain exact-CodePudding

in my application, clients can choose multiple words to exclude from their search. These words are run against a FullTextSearch field.

I am wondering if there's a way I can have a NOT containing exact match with full-text search.

for example, the field contains

Record 1 - `Oranges, Apples, BloodOranges`
Record 2 - `Oranges`
Record 3 - `BloodOranges`
Record 4 - `Apples`

If the customer does a filter of oranges, it'll filter Oranges and BloodOranges. I would like it to return records 3,4.

SELECT * 
FROM Contacts 
WHERE  Contains (taglist, 'Oranges')

CodePudding user response：

In SQL-Server you can use the CONTAINSTABLE
For example:
This returns you the Contacts that contain the exact phrase "Oranges"

SELECT *, k.[Rank]
FROM Contacts c
INNER JOIN CONTAINSTABLE([dbo].[Contacts], taglist, N'"Oranges"') as [k]
    ON c.Id = k.[Key]

And for what you want, you can write something like this:

;With CTE AS
(
    SELECT c.Id
    FROM Contacts c
    INNER JOIN CONTAINSTABLE([dbo].[Contacts], taglist, N'"Oranges"') as [k]
        ON c.Id = k.[Key]
)
Select * From Contacts CC
Where CC.Id not in (Select Id From CTE)