SQL Server Full-text search for keywords without prefix matching I have the following text in my NVARCHAR column 


  "Ich bin eine SuperbProdukt"


I have a full-text search enabled on the column and I can search for it by executing the following query that I wrote : 

SELECT FROM MY_TABLE WHERE CONTAINS(Title, ' "Ich*" ')


The query gives me all the result that contains my search criteria, I can also search for other words within the whole text such as "Superb" and get the same record back. However, I am trying to have a search functionality that returns results even when I give keywords that are not the beginning of any words in a phrase, but rather it's in between. For instance, I am expecting to get the same record back when searching for "Produkt"

Is there a way to achieve this in SQL server without using LIKE operation? I heard that CharIndex can do the job, but I have no clue how that can be helpful in my scenario.

P.S: I am using SQL SERVER 2012 on Azure.
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.