Full text search asterisk returns wrong result

Question

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.

Then you should not use contains, use like 'n*' if you want i to start with N. — Brad, Jun 27 '18 at 17:01
Name field can has more than one word, and I want search 'n' char in every. For example CONTAINS(Name, N'"lol*" and "lala*"') exactly finds "name name lollll asd lalaaaa" string. — Frad2, Jun 27 '18 at 17:10

score 0 · Accepted Answer · answered Oct 12 '18 at 12:28

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.

1 Answers1