Search code examples
sql-serverfull-text-searchcontainstable

SQL Server Full Text Index Contains search exact match containing "it"

I'm fairly new to Full Text Index in SQL server. It has been working really well for me however, recently someone did an exact match search for "IT Manager" and the "IT" part of the search seems to be ignored.

e.g.

SELECT * FROM CONTAINSTABLE(vCandidateSearch, SearchText, '"it manager"')

and 

SELECT * FROM CONTAINSTABLE(vCandidateSearch, SearchText, '"manager"')

return the same results. What am I doing wrong?

Solution

  • "" doesn't mean an exact match. It just looks for that phrase in the text.

    If I have a value The big red house

    Example matches

    "big red house"
"big"
"house"
"red house"

    Example of a non match

    "the big yellow"

    If you need that only "The big red house" matches then you might be better off creating a non-clustered index on that column and using a regular = predicate