FTS CONTAINS not working

Hi There,

I have table called tblFileInfo, full text indexing is done on the field KEYWORDS
FTS is enabled on the server. I have done full population.
If I use Like 21 results are returned, but using Contains is not returning any results what may be the cause.

0 results returned
Select * From tblFileInfo Where Contains(Keywords,'abc')

21 results returned
Select * From tblFileInfo Where Keywords like '%abc%'

Regards
Rohan

Please note that those two queries are not same.

Contains(keywords, 'abc') will return rows has exact word 'abc'.

like '%abc%' will return any row which contains substring 'abc', for example, rows contains "fooabcbar", "foo abcbar", etc.

So depend on the data in your table, you may get match by like query but not contains query.

thanks,

Jingwei

|||

Hi Jingwei,

in the field KEYWORDS there are values as ABC which CONTAINS may return normally, not 21 but atleast records having abc as a whole word should be returned but it not happening, CONTAINS is not working at all.

Pls Help

Regards
Rohan

|||

What is itemcount on the full-text index? You can use objectproperty(<table_id>, 'TableFulltextItemCount') to get it. "abc" is not in noise word list and contains should be able to find it. Please check SQL<dbid>+<catalogid>.LOG file under sql LOG directory to see if there is any suspecials error.

If you can attach a repro script, I can try out on my machine.

thanks

|||Hi Rohan,

I'm currently marking one of Jingwei's replies as a correct answer until you provide more information for us to reproduce the problem. You can unmark the question as being a correct answer and this will bring it to our attention again.

Alternatively, if you found a resolution to your problem, please post it here as it will help others troublehsooting similar problems.

Regards,
Boris.