Can't understand difference in fulltext results - contains, contains with wildcard, freetext
The difference is caused by noise word &quot;W&quot;. By default SQL Server uses stoplist to exclude some short or frequently used words like &quot;and&quot;, &quot;does&quot;, &quot;could&quot;. You can see the full list using such query:
select * from sys.fulltext_system_stopwords where language_id = 1033

And you can disable the stoplist for your full text index if you want.
Now let's take a look at your queries:
SELECT * FROM InvoicePDFContent t1 WHERE CONTAINS(t1.Filecontent, '&quot;W*&quot;')

this one looks for all rows which contain words starting with &quot;W&quot; and these words shouldn't be in your stoplist.


SELECT * FROM InvoicePDFContent t1 WHERE CONTAINS(t1.Filecontent, '&quot;W&quot;')

this query returns nothing because you are looking for a stop word.


SELECT * FROM InvoicePDFContent t1 WHERE CONTAINS(t1.Filecontent, '&quot;W 917&quot;')

this query just ignores the W and looks for &quot;917&quot;.


SELECT * FROM InvoicePDFContent t1 WHERE CONTAINS(t1.Filecontent, '&quot;W 917*&quot;')

This is the most interesting case. According to SQL Server documentation:

When &lt;prefix_term&gt; is a phrase, each word contained in the phrase is
considered to be a separate prefix. Therefore, a query specifying a
prefix term of &quot;local wine*&quot; matches any rows with the text of &quot;local
winery&quot;, &quot;locally wined and dined&quot;, and so on.

It means actually you are looking for phrases which have two words in a row. The first one is like &quot;W*&quot; (but it shouldn't be in stoplist) and the second one is &quot;917*&quot;.