SQL Server Full Text Search Problems With Noise (Stop) Words

Full text search is great when it is working as it should.

Sometimes it doesn’t quite work as expected though and you may find yourself troubleshooting a tricky problem.

Recently I ran into an issue where stop words were causing issues for searches that were using the contains function.

In this particular problem no matter where the stop word occurred in the search string no results were coming through.

Lets take a look at the problem and then we can dive more deeply into some of the other areas of full text search.

BTW – we use the phrases “noise words” and “stop words” interchangeably – they mean the same thing.


Image: UnSplash

Issue with stop words in full text search

Let me give you an example of what I mean.

If I had a table/column that I was doing a full text search on and it had ten records with the text containing something like “how to get a good pair of green shoes” when searching for a string like ‘get and green and shoes’ I would expect to get the relevant records returned.

Unfortunately though if the word ‘get’ was on our stop list we would receive no results at all.

Searching for ‘green and shoes’ would get us results.

As you can see this is far from satisfactory. Fortunately there is a simple fix.

There is a setting you can change to ensure that the search still continues, despite the stop words.

You need to change transform noise words setting as below:

sp_configure 'show advanced options', 1
RECONFIGURE
GO
sp_configure 'transform noise words', 1
RECONFIGURE
GO

Check how the noise words are being handled by Full Text Search

To check how a full text search is parsing search words you can run this:

Select * from sys.dm_fts_parser('get and green and shoes', 1033, 5, 0)  

Check out BOL for more reading on dm_fts_parser and the parameters.

How to add or remove words from a stop list

Going back to our orginal example, if we now decided we wanted to remove the word “get” from our list (in English) we could run the following command:

ALTER FULLTEXT STOPLIST DROP 'get' LANGUAGE 'English';   

If we then decided we would like to put it back we would run this:

ALTER FULLTEXT STOPLIST ADD 'get' LANGUAGE 'English';   

Note: You will need to rebuild the indexes after adding or removing words.

How to rebuild your full text catalog

To rebuild your catalog via T-SQL run the following syntax.

ALTER FULLTEXT CATALOG REBUILD

Tip: If you are rebuilding a full text catalog and want to see how long it is going to take there is a great query I found here that shows percentage complete.

Full text search can be a confusing beast to troubleshoot at times.

Make sure you reach out to us if you have any major problems.

Rob StGeorge

Senior SQL Server Database Administrator residing in Auckland, NZ

Leave a Reply