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 :


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.

1 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:

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.

  • That was exactly what I was looking for. Thank you.
    – Benjamin
    Nov 26, 2019 at 21:07

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

Not the answer you're looking for? Browse other questions tagged or ask your own question.