CODEWITHSUNDEEP
sql-servert-sqlfull-text-searchfull-text-indexing
George Menoutis
George Menoutis

Reputation: 7240

Full text index doesn't work at single word?

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!

Upvotes: 0

Views: 119

Answers (1)

sniperd
sniperd

Reputation: 5274

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

Upvotes: 1

Related Questions