sql-serverfull-text-searchfulltext-indexfull-text-catalog

Full text search asterisk returns wrong result

I have a Ship table with FTS index, which was created as: 

CREATE FULLTEXT INDEX ON Ship 
(       
    Name 
)
KEY INDEX PK_Ship_Id
ON MyCatalog
WITH CHANGE_TRACKING AUTO, STOPLIST OFF;

And when I run query bellow:

select Name From Ship where CONTAINS(Name, N'"n*"');

I get wrong result, for instance "Vitamin D3 1000 Iu". But I want get only rows where name field has any word starts with 'n' char.

Solution

  • FTS engine has strange 'feature', when you try find somethings as CONTAINS(Name, N'"n*"'), it searches all numbers because it keeps numbers as NN.

    The best decision which was founded is in these two cases(CONTAINS(Name, N'"n*"'), CONTAINS(Name, N'"nn*"')) use "like" search.