Using the NOT keyword with full-text search and multiple columns
Can I use the CONTAINSTABLE and NOT keywords with SQL Server full-text searches and exclude rows where one column contains the value that I want to exclude?
For example, let's say the following indexed view (simplified to illustrate this problem):
ItemId INT
FirstName VARCHAR(200)
MiddleName VARCHAR(200)
LastName VARCHAR(200)
Address VARCHAR(1000)
ChildrenNames TEXT
SearchData TEXT
SearchData is a concatenated field generated from other text / varchar fields.
This search can then be found with the following query to find rows containing name but not lastname:
SELECT *
FROM [v_MyView] V
LEFT OUTER JOIN CONTAINSTABLE
(
[v_MyView],
(
[FirstName],
[MiddleName],
[LastName],
[Address],
[ChildrenName],
[SearchData]
), '"name" AND NOT "lastname"') FTS ON [FTS].[Key] = [V].[ItemId]
WHERE (ISNULL(RANK,0) > 0)
This query does not seem to return the correct results, as it will only exclude rows if ALL of the search fields meet the criteria, whereas that should exclude ANY rows that match the criteria.
m. Rows containing first and last name in column any (single) should be excluded from the result set, not just rows containing first and last name in column each (which goes unlikely).
The only available option is to check if the custom query contains any exclusion values (for example, -lastname), and if so, restrict the query to search for the "SearchData" column only if it is a concatenated column and will contain all the data. However, this will affect relevancy rankings and will not be a good decision.
source to share
I faced the same problem, I suspect it is a bug in the SQL server.
I suspect someone forgot! (A & B & C) ===! A |! B |! C
Now I am creating a new column to store the concatenation of all the strings you want to search for, add triggers to add all text to the new column when data is inserted or updated, and just search for that new column.
This will normalize your table, but it seems to preform intelligently.
source to share