SQL Contains not working on full text index with preceding *

Question

select top 10 * from table where contains(*,'"abc*"') 

works and returns 6 rows

abcdef

abcd

abcd

abcde

ABCDEFGH

ABCDEFG

select top 10 * from table where contains(*,'"*bc*"') 

returns no records found.

Does anyone know how I can get the '"*bc*"' function to work ?

Answer 1

Leading wildcard searches will exclude the use of any index... including full-text indexing.

So "*bc*" isn't compatible with full-text indexing... and a non-full-text search such as LIKE '%bc%' will result in a full-table scan.

Related Questions:

SQL Server Full Text Search Leading Wildcard

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

Answer 2

Try

select top 10 * from table where contains(*,'%bc%') 

or

select op 10 * from table where COLUMN_NAME LIKE '%bc%'