SQL Server Full-text search for keywords without prefix matching

Question

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.

Answer 1

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) <> 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.