2

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.

Aasim Abdullah
  • 2,729
  • 3
  • 21
  • 37
Stackoverflowuser
  • 1,506
  • 2
  • 23
  • 40
  • It might be stupid, but did you check without `" "` or with `"*jon*"`? – Kamil Gosciminski Nov 05 '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 05 '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 05 '14 at 12:37
  • 1
    @Stackoverflowuser see http://dba.stackexchange.com/a/8644/38055 – Kamil Gosciminski Nov 05 '14 at 12:38
  • Guys the word 'jon' might be a stopword in our database, a word which is useful in some other language. Working on how to check the stoplists and remove a keyword out of it. Thanks. – Stackoverflowuser Nov 05 '14 at 12:47
  • How can I remove some English token words (such as "a" or "as" etc) from the stoplist? Thank you. – Stackoverflowuser Nov 05 '14 at 12:55
  • 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](http://msdn.microsoft.com/en-us/library/cc280871.aspx) 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 05 '14 at 13:30
  • Thanks wBob I dont't want to use another stoplist. System is just fine, if only I could remove one particular word off it. – Stackoverflowuser Nov 05 '14 at 13:49

1 Answers1

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  
     }  
;
John K. N.
  • 14,660
  • 9
  • 40
  • 93