0

My search phrase is "test doc".

I have a query with full-text search phrase like this:

SELECT doc.iEntityId, doc.strName, doc.iHandbookId
FROM m136_tblDocument doc
WHERE iLatestApproved = 1
AND doc.iDeleted = 0
AND (
(CONTAINS(doc.strName, '"test doc*"')))

The result as this picture: result

But if I try to put search phrase is "test doc 3".

It gives no hit. result.

My expected will give 3 hits: test docyument 23, test doc 3 and test doc 1.3.1

Anyone can help me?

Thank in advance.

jack.pop
  • 683
  • 2
  • 6
  • 21

1 Answers1

0

Full text is aimed for texts, big texts. In your example you're searching for something small. Small is any text shorter than about 100 chars.

This code will work for you always.

SELECT doc.iEntityId, doc.strName, doc.iHandbookId
FROM m136_tblDocument doc
WHERE iLatestApproved = 1
AND doc.iDeleted = 0
AND doc.strName LIKE '%test doc 3%'

Using full-text may be a bit problematic. There were found bugs for the feature on Microsoft connect. In order to use the feature properly you have to read BOLs for it. For example this one is describing the feature well - https://www.simple-talk.com/sql/learn-sql-server/sql-server-full-text-search-language-features/

For your case, my advise is to replace it with LIKE, and no worries. Indexes can improve the query performance. Make a short tuning for it to see whether you need an index.

Igor Micev
  • 1,514
  • 1
  • 17
  • 23
  • doc.strName LIKE '%test doc 3%' does not give 3 hits as expected. – jack.pop Jul 05 '16 at 08:27
  • Hm, but you have to use '%test doc%3%' for your data. – Igor Micev Jul 05 '16 at 09:38
  • Yes, use LIKE '%test doc%3%' seem works fine. So we might split phrase into words by white space. Then we will build search phrase by putting % for each word. Thank for your idea. It is good approach :) – jack.pop Jul 05 '16 at 10:40