Contains clause in SQL Server full-text search omits some matching rows

Question

I have a table called Products in a SQL Server 2014 database. A column ProductName in this table has a regular non-clustered index as well as a full-text index on it. When I run the queries below, then the full-text search queries omit some of the rows that a LIKE clause includes.

Question: Why are the first two full-text queries not bringing up products that are returned by the LIKE query? My goal is to look for Products whose name contains i2.

SELECT p.ProductId, p.ProductName FROM Products p WHERE contains(p.ProductName, N'i2');
SELECT p.ProductId, p.ProductName FROM Products p WHERE freetext(p.ProductName, N'i2');
SELECT p.ProductId, p.ProductName  FROM Products p WHERE p.ProductName LIKE '%i2%';

The results that I am seeing from these queries is as in screenshot below.

Answer 1

This is due to the way that fulltext search works, it's different to the way like works as it is not doing simple pattern matching it is language aware and is actually looking for words, hence why it finds the row with the "word" i2 in it, but not the one (id 352) with the "word" id21 for example.

What you can do if you want to use fulltext search is to perform a prefix search, where you can search for words that start with i2, like this:

SELECT p.ProductId, p.ProductName FROM Products p WHERE contains(p.ProductName, N'"i2*"');

Note that you cannot search for i2 in the middle or end of a word like N'"*i2*"' or N'"*i2"' as in this case the asterix is ignored as standard punctuation and is not treated as a wildcard.