Search for part of the word in the phrase with full text search in SQL Server 2016

Question

In the Microsoft SQL Server, our searches are limited to starting words when we use a full-text search to search for values. That is, we cannot search contains the word looks like the LIKE operator in the middle.

I try to execute this query but the result is not my opinion.

I want to search for the middle of the term. For example, if my term is "Microsoft" and my query is :

SELECT * 
FROM dbo.SMS_Outbox 
WHERE CONTAINS(MessageText, N'"*soft*"')  

There is no result returned!

Satisfaction

That isn't what a full text index does. It indexes the words, not parts of it. You'll have to use LIKE to do this; which won't be SARGable. – Quadruplex

Answer 1

The documentation is quite clear that wildcards are allowed only at the end of search terms:

The CONTAINS predicate supports the use of the asterisk (*) as a wildcard character to represent words and phrases. You can add the asterisk only at the end of the word or phrase. The presence of the asterisk enables the prefix-matching mode. In this mode, matches are returned if the column contains the specified search word followed by zero or more other characters.

You cannot do what you want easily. One simple option is to switch to LIKE and take the performance hit:

WHERE MessageText LIKE N'%soft%'

Another option might be to parse your text in such a way that soft is always at the beginning of a search term.

Micrometeorite

WHERE MessageText LIKE N'%soft%' would it still benefit from a FULLTEXT index or is it just a regular index scan on MessageText? – Kata