Full text catalog missing a keyword

Question

We have a full text catalog on one of our databases but one keyword does not work.

e.g:

select * from table where name = 'jon' -- result set returns as expected  
select * from table where Contains(a.Name, '"jon"') -- no result set

What might be causing this?

Thank you.

Answer 1

You might want to have a look at the following article:

Configure and Manage Stopwords and Stoplists for Full-Text Search (Microsoft Docs)

To prevent a full-text index from becoming bloated, SQL Server has a mechanism that discards commonly occurring strings that do not help the search. These discarded strings are called stopwords. During index creation, the Full-Text Engine omits stopwords from the full-text index. This means that full-text queries will not search on stopwords.

It contains a general overview of how stoplists work and references a further article that goes on to explain how you can modify stoplists to achieve what you are looking for:

ALTER FULLTEXT STOPLIST (Transact-SQL) (Microsoft Docs)

Inserts or deletes a stop word in the default full-text stoplist of the current database.

The syntax of the command is as follows:

ALTER FULLTEXT STOPLIST stoplist_name  
{   
        ADD [N] 'stopword' LANGUAGE language_term    
  | DROP   
    {  
        'stopword' LANGUAGE language_term   
      | ALL LANGUAGE language_term   
      | ALL  
     }  
;