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

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 ?

score 1 · Accepted Answer · edited May 23 '17 at 12:18

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:

edited May 23 '17 at 12:18

Community

answered Jan 15 '13 at 18:44

Merr Leader · Answer 2 · 2013-01-15T18:48:36.373

0

Try

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

or

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

edited Jan 15 '13 at 18:48

answered Jan 15 '13 at 18:41

Merr Leader

2 Answers2