SQL Server Full-text search for keywords without prefix matching
Yes, it's possible to achieve what you described using CHARINDEX. 
According to this remark:


  If CHARINDEX does not find expressionToFind within expressionToSearch,
  CHARINDEX returns 0.


Knowing that you just have to filter the rows like this:

DECLARE @expressionToFind varchar(50)  = 'an';

SELECT MiddleName
FROM AdventureWorks.Person.Person
WHERE CHARINDEX(@expressionToFind, MiddleName, 1) &lt;&gt; 0;


Here's the result:



As you can see, it brings not only the middle names that begin with an but also the ones with an in the middle of the text.

A little remark regarding this method is that differently from using the Full-Text Search it will scan your table. It might (and almost certainly will) have a big performance impact.