sqlsql-server-2008full-text-searchfulltext-indexfull-text-catalog

SQL Contains not working on full text index with preceding *

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 ?

Solution

  • 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?