Sql-server – Full text catalog missing a keyword

catalogsfull-text-searchsql serversql-server-2008sql-server-2008-r2

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.

Best Answer

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  
     }  
;

Related Solutions

Sql-server – How to set full text catalog location SQL Server 2008/2012

Create a ft catalog
Create a filegroup for your ft index
Create a file in the filegroup you created above on the drive you want the ft index on
Right-click on the table you want to create the ft index for
Select Full-text Index -> Define Full-text index...
On the first page of the wizard, click Next
Select the unique index to use, click Next
Select the columns to index, click Next
Select the preferred change tracking, click Next
Select the ft catalog you created
Use the Select index filegroup pulldown to select the ft filegroup you created
Click Next
Create a schedule if you wish, click Next
Click Finish, click Close
Richt-click the table, select Full-text Index -> Properties
Verify the correct filegroup is indicated

Sql-server – Full-text search in SQL Server on multiple tables fails

Hi we can query multiple tables in a full-text search by using views ,but we some advantages and disadvantages . Here in your case you created view dbo.vw_recipe_search it seems your are not using it in altered procedure when i query it i am getting results as per intended

  SELECT *
        FROM dbo.vw_recipe_search r 
        INNER JOIN FREETEXTTABLE(vw_recipe_search, *, 'salsa peas') kt
            ON r.search_id = kt.[KEY]
        ORDER BY RANK DESC;

The problem in this approach is ranking and weightage of each column , like suppose if u want to give high weight to title column than descrip you have to do a lot of work in order by rank column . We used Levenstein string comparision function to order for better results set

Best Answer

Related Solutions

Sql-server – How to set full text catalog location SQL Server 2008/2012

Sql-server – Full-text search in SQL Server on multiple tables fails

Related Question