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

Reputation: 135

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 ?

Upvotes: 1

Views: 2496

Answers (2)

Merr Leader
Merr Leader

Reputation: 737

Try 

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

or

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

Upvotes: 0

Michael Fredrickson
Michael Fredrickson

Reputation: 37388

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?

Upvotes: 1

Related Questions