question

ljanicke avatar image
ljanicke asked

Full Text Search not returning expected result

I am doing a full text search for a document called "Document 1". The following sql does not return the record. However, if I change the 1 to a 10, it returns "Document 10" select * from document_keyword where CONTAINS (keyword,'Document AND 1')
sql-serverfull-textcontainsnoise-wordsstop-words
3 comments
10 |1200

Up to 2 attachments (including images) can be used with a maximum of 512.0 KiB each and 1.0 MiB total.

What happens if you try
select * 
    from document_keyword 
    where CONTAINS (keyword,'"Document 1"');
? The problem in your original query could be caused by the fact that the term **1** is too short (only one character in length).
1 Like 1 ·
Well, the returns all that have the keyword 'document'. Looks like it ignores the 1 altogether RESULTS: Document 1 Document 2 Document 3 Document 10 Document 11 Document Description If I change to select * from document_keyword where CONTAINS (keyword,'"Document 10"'), it just returns the one row where keyword = Document10
0 Likes 0 ·
If I follow the proper **AND** operator syntax. It should have been like this select * from document_keyword where CONTAINS (keyword,' "Document" AND "1" ') The two contains search conditions must be double quoted. But I guess that would not give any result too. This is where I agree with @Oleg that CONTAINS can search for a word or a phrase, but for a single character it may not be performing as desired.
0 Likes 0 ·

1 Answer

·
Usman Butt avatar image
Usman Butt answered
Oh...I forgot that single characters such as a,A,1,2 are considered to be noise/stop words and are left out of the full-text index since they are empirically known to be useless to a search. So may be, you have to revisit your stop words to make it work. Hope it helps.
1 comment
10 |1200

Up to 2 attachments (including images) can be used with a maximum of 512.0 KiB each and 1.0 MiB total.

Thanks for the assist. I will look into stopwords and stoplists.
0 Likes 0 ·

Write an Answer

Hint: Notify or tag a user in this post by typing @username.

Up to 2 attachments (including images) can be used with a maximum of 512.0 KiB each and 1.0 MiB total.