sql server 2017 fulltext search

adele vance 1 Reputation point
2021-04-02T08:58:59.683+00:00

Hi There,
I've tested some kinds of Full-Text Search feature with SQL 2017.

When I tried to search middle word without any space, I can't see the result what I want.
You can check below screenshot for details.

83992-image.png

When I executed query HIGHLIGTHED purple color, There is no "Freewheel" value in the result.

How can I query for checking all of words including "wheel"?
Any advice would be appreciated.

SQL Server
SQL Server
A family of Microsoft relational database management and analysis systems for e-commerce, line-of-business, and data warehousing solutions.
12,629 questions
Transact-SQL
Transact-SQL
A Microsoft extension to the ANSI SQL language that includes procedural programming, local variables, and various support functions.
4,547 questions

2 answers

Sort by: Most helpful
Most helpful Newest Oldest
  1. MelissaMa-MSFT 24,176 Reputation points
    2021-04-02T09:17:53.453+00:00

    Hi @adele vance ,

    Welcome to Microsoft Q&A!

    Please have a try with CONTAINS. 

    SELECT Name  
FROM Production.Product    
WHERE CONTAINS(Name, 'wheel');

    CONTAINS finds all products that contain the word "Wheel" while FREETEXT searches for all products that contain words related to "Wheel".

    You could refer more details in this link.

    Best regards
    Melissa

    If the answer is helpful, please click "Accept Answer" and upvote it.
    Note: Please follow the steps in our documentation to enable e-mail notifications if you want to receive the related email notification for this thread.

    0 comments
  2. Erland Sommarskog 100.8K Reputation points MVP
    2021-04-02T09:38:12.427+00:00

    You would need to use LIKE: 

    SELECT Name FROM Production.Product WERE Name LIKE '%wheel%'

    Full-text indexes works on words. What is a word is defined by the word-breaker, which is language specific. But typically a word is something delimited by spaces and punctuation. It would require quite a bit more logic to break up "freewheel" in "free" and "wheel".

    So if you want to find parts of wordss, FREETEXT or CONTAINS are not going to help you.

    0 comments