Sql-server – SQL Server Full-text search for keywords without prefix matching

azure-sql-databasefull-text-searchsql server

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.

Best Answer

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:

enter image description here

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.

Related Question