SQL Server Full Text Search Leading Wildcard

Question

After taking a look at this SO question and doing my own research, it appears that you cannot have a leading wildcard while using full text search.

So in the most simple example, if I have a Table with 1 column like below:

TABLE1

coin
coinage
undercoin

select COLUMN1 from TABLE1 where COLUMN1 LIKE '%coin%' Would get me the results I want.

How can I get the exact same results with FULL TEXT SEARCH enabled on the column?

The following two queries return the exact same data, which is not exactly what I want.

SELECT COLUMN1 FROM TABLE1 WHERE CONTAINS(COLUMN1, '"coin*"')

SELECT COLUMN1 FROM TABLE1 WHERE CONTAINS(COLUMN1, '"*coin*"')

Answer 1

Full text search works on finding words or stems of words. Thus, it does not find the word "coin" anywhere in "undercoin". What you seek is the ability search suffixes using full text searches and it does not do this natively. There are some hacky workarounds like creating a reverse index and searching on "nioc".