Can't understand difference in fulltext results - contains, contains with wildcard, freetext I have a table with an fulltext index on the column named Filecontent.
The table has a row where content contains &quot;W 917&quot;. For context, the content column on this rows contains much more than just what I'm searching for.
I don't understand why I'm getting different results depending on whether I'm using contains, contains with wildcard or freetext.
Why is CONTAINS without wildcard getting results, but CONTAINS with wildcard doesn't?
-- Searching for &quot;W 917&quot;
-- No match - CONTAINS with wildcard
SELECT * FROM InvoicePDFContent t1 WHERE CONTAINS(t1.Filecontent, '&quot;W 917*&quot;')

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

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


-- Searching for &quot;W&quot;
-- Match - CONTAINS with wildcard
SELECT * FROM InvoicePDFContent t1 WHERE CONTAINS(t1.Filecontent, '&quot;W*&quot;')

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

-- No match - FREETEXT
SELECT * FROM InvoicePDFContent t1 WHERE FREETEXT(t1.Filecontent, '&quot;W&quot;')
´´´
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;.