How do you get leading wildcard full-text searches to work in SQL Server?

Solution 1:

Workaround only for leading wildcard:

• store the text reversed in a different field (or in materialised view)
• create a full text index on this column

• find the reversed text with an *

  SELECT * 
  FROM TABLENAME 
  WHERE CONTAINS(TextColumnREV, '"mrethcraes*"');
  

Of course there are many drawbacks, just for quick workaround...

Not to mention CONTAINSTABLE...

Solution 2:

The problem with leading Wildcards: They cannot be indexed, hence you're doing a full table scan.

Solution 3:

It is possible to use the wildcard "*" at the end of the word or phrase (prefix search).

For example, this query will find all "datab", "database", "databases" ...

SELECT * FROM SomeTable WHERE CONTAINS(ColumnName, '"datab*"')

But, unforutnately, it is not possible to search with leading wildcard.

For example, this query will not find "database"

SELECT * FROM SomeTable WHERE CONTAINS(ColumnName, '"*abase"')