0

I have a table in SQL 2012 that I'm performing a full text search on.

One of the records has, as a part of a larger string, the text 'Trying out your system'.

The problem is that, if I search for two words in the target string which are too close together, I don't get a match. 

select * from mytable where contains(*,'trying') -- match
select * from mytable where contains(*,'trying and out') -- no match
select * from mytable where contains(*,'trying and your') -- no match
select * from mytable where contains(*,'trying and system') -- match

I'm aware that I can search for an exact string by enclosing the search pattern in double quotes, however that's not really what I'm after.

Any suggestions how I can make all of the above search terms match?

Thanks.

Damien Sawyer
  • 5,323
  • 3
  • 44
  • 56

1 Answers1

1

This sounds like an issue with stopwords (common words like "the", "your", etc. that are usually filtered out of the full text index, thus you cannot search on them).

To prevent this from happening, you can modify your full text index so that it does not use a stoplist (in other words, every single word will be indexed and thus searchable).

ALTER FULLTEXT INDEX ON MyTable SET STOPLIST = OFF

Be sure to rebuild the full text catalog afterwards.

But only do this if you really need the ability to search on common words. Typically this is not necessary. Also, doing so may slow down your full text searches.

Keith
  • 20,636
  • 11
  • 84
  • 125
  • Thanks Keith. I think that you're right. I stumbled upon this post as well which is interesting. http://stackoverflow.com/a/10451332/494635 – Damien Sawyer Nov 06 '15 at 20:46