SQL Server Full Text Search - Is it possible to search in the middle of a word?

Question

I have full text search on my database.

Is it possible to search in the middle of a word for some text?

For example, I have a column Description that contains the following text:

Revolution

Is it possible to search for 'EVO' and have it find it in the word Revolution or am I stuck doing a LIKE:

SELECT * FROM Table WHERE Description LIKE '%EVO%'

Is there a FTS equivalent of the above query?

EDIT
I want to make it clear what I am trying to ask because it appear a few people might be confused. I believe that SQL Server FTS can only search at the beginning of the word (prefix search). So if I query like:

SELECT * FROM Table WHERE CONTAINS(Description, '"Revo*"')

Then it will find the word Revolution. I want to know if it is possible at all to search something in the MIDDLE of the word. Not at the end. Not at the beginning. From what it looks like this is not possible and it makes sense because how would SQL server index this, but I just wanted to be certain.

Crimpy

Does this answer your question? Search for part of the word in the phrase with full text search in SQL Server 2016 – Yamashita

Answer 1

This looks like it has come up before and the short answer was "No". Previous thread

Askwith

Answer 2

You can use CONTAINS. See this link

Full text catalog/index search for %book%

Life

The REVERSE column thing mentioned in that does not work. EVO is in the exact middle of the word. It sounds like this is not possible with full text search just because it can only do prefix searching. – Crimpy

@Dismissile, Yes kind a true; CONTAINS will do kind of wild card searching; may not be as good for fulltext search. I did gone through the post before linking here and kind a fell that it may not be possible what you are asking for but still thought of posting here and see if it helps. – Life

Answer 3

The only way to do this search is to add a "rotational" break down of the words. As an exemple, the word "locomotion" will be break down into 9 new "word" like : "ocomotion" "comotion" "omotion" "motion" "otion" "tion" "ion" "on" "n" So now you can put this table into the Full Text Search (or create à new columns with all these parts of the word) to find it quickly.

I wrote a paper to do that without FTS (but it is in french) : https://blog.developpez.com/sqlpro/p13123/langage-sql-norme/like-mot-ou-les-index-rotatifs

Enliven

Answer 4

You can use CONTAINS instead of LIKE.

SELECT * 
  FROM Table 
 WHERE CONTAINS(Description, '"EVO*"')

Jenifferjenilee

@Dismissile: I have just re-read the documentation, and it can only be prefixed. So, "evo*" will bring back "evolution" and not "revolution". I don't think that you'll be able to use a FTS with this query. – Jenifferjenilee

@Neil Knight - That's what I was thinking. – Crimpy