sql-serverfull-text-searchmatch-phrase

SQL: full-text search does not work if search phrase has number inside?

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.

Solution

  • 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.