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


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.

  • It might be stupid, but did you check without " " or with "*jon*"? – Kamil Gosciminski Nov 5 '14 at 12:31
  • Yes mate sorry it didn't work :( btw does rebuilding the catalog repopulates the index? I just started the process. Thanks. – Stackoverflowuser Nov 5 '14 at 12:35
  • 1
    It's more likely the full-text index hasn't finished populating. How big is the table? Check on the status of the rebuild using DMV sys.fulltext_indexes – wBob Nov 5 '14 at 12:37
  • 1
    @Stackoverflowuser see dba.stackexchange.com/a/8644/38055 – Kamil Gosciminski Nov 5 '14 at 12:38
  • 1
    You can change your full-text index to use a custom (rather than the system) stop list, then alter it using ALTER FULLTEXT STOPLIST link However I would question the need to do this. If someone does actually search for documents containing "a" that will then be most of your documents, somewhat defeating the purpose of full-text indexing. – wBob Nov 5 '14 at 13:30

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:

        ADD [N] 'stopword' LANGUAGE language_term    
  | DROP   
        'stopword' LANGUAGE language_term   
      | ALL LANGUAGE language_term   
      | ALL  
| improve this answer | |

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.