0

I have a full text index on many columns of the customer table, one of which columns is fname.

The following query:

select * from customer where fname like 'In%' and code='1409584557891'

returns me the line needed, this customer has an fname of 'In' .But if I add this to the end:

and contains((customer.fname) , N'"In*"')

an empty result-set is retuned. Why?

Also: there is another column named lname. If I add the equivelant contains command with the column and its value altered, it works!

George Menoutis
  • 6,894
  • 3
  • 19
  • 43
  • 1
    There is a good chance "In" is a noise word. I also believe that if you do a fulltextsearch for something too short like the letter 'a' it is simply considered a noise word. See if 'a' gives you anything. https://www.mssqltips.com/sqlservertip/1491/sql-server-full-text-search-noise-words-and-thesaurus-configurations/ – sniperd Jul 12 '18 at 12:53
  • well, "i" gives nothing. I consider your comment an answer. – George Menoutis Jul 12 '18 at 13:08
  • cool, I'll post a nice little write up – sniperd Jul 12 '18 at 13:15

1 Answers1

1

There is a good chance "In" is a noise word. I also believe that if you do a fulltextsearch for something too short like the letter 'a' it is simply considered a noise word. See if 'a' or 'I' gives you anything.

Here is a link that can provide information on changing the noise words around if that is the case.

https://www.mssqltips.com/sqlservertip/1491/sql-server-full-text-search-noise-words-and-thesaurus-configurations/

You may also be able to simply turn off noise or 'stop' words:

https://dba.stackexchange.com/questions/135062/sql-server-no-search-results-caused-by-noise-words

sniperd
  • 5,124
  • 6
  • 28
  • 44