SQL Full text search Contains command

Question

I am trying to figure out why these 2 statements act differently

SELECT DISTINCT FIELD1, FIELD2
FROM SomeTable
WHERE CONTAINS(*,'WORD1') AND CONTAINS(*,'WORD2*') AND CONTAINS(*,'WORD3')

SELECT DISTINCT FIELD1, FIELD2
FROM SomeTable
WHERE CONTAINS(*,'"WORD1" AND "WORD2*" AND "WORD3"')

I am specifying ALL indexed fields for my Full Text Search so why does the 2nd command return nothing but the first command returns the expected results? I want to search accross all fields where any of the fields has WORD1 and WORD2 and WORD3, in one CONTAINS statement.

Answer 1

I have ran into this issue before and it is by design. See this MS support article.

Assume you have two columns c1 and c2. The query you listed

CONTAINS(*,'"WORD1" AND "WORD2*" AND "WORD3"')

is interperted as

CONTAINS(c1,'"WORD1" AND "WORD2*" AND "WORD3"') or CONTAINS(c2,'"WORD1" AND "WORD2*" AND "WORD3"')

So EVERY WORD must be in EITHER column (c1 or c2). It will not return results if WORD1 is in c1 and WORD3 is in c2.

Your first query works because it is interperted as

(CONTAINS(c1,'WORD1') OR CONTAINS(c2,'WORD1')) AND (CONTAINS(c1,'WORD2*') OR CONTAINS(c2,'WORD2*')) AND (CONTAINS(c1,'WORD3') OR CONTAINS(c2,'WORD3'))

in this (the first case) different words can exists in different columns as long as every word exists in at least one of the columns.

Answer 2

It looks like your second statement is searching for the entire string <"WORD1" AND "WORD2*" AND "WORD3">. That is why it returns no results. Also, as written, the first would also be searching for <"WORD1"> with the double quotes. Does it return any results of <"WORD1">?