question

adelevance-8185 avatar image
0 Votes"
adelevance-8185 asked MelissaMa-msft commented

sql server 2017 fulltext search

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-generalsql-server-transact-sql
image.png (193.1 KiB)
· 1
5 |1600 characters needed characters left characters exceeded

Up to 10 attachments (including images) can be used with a maximum of 3.0 MiB each and 30.0 MiB total.

Hi @adelevance-8185,

Could you please validate all the answers so far and provide any update?

Please remember to accept the answers if they helped. Your action would be helpful to other users who encounter the same issue and read this thread. 

Thank you for understanding!

Best regards
Melissa

0 Votes 0 ·
MelissaMa-msft avatar image
0 Votes"
MelissaMa-msft answered MelissaMa-msft edited

Hi @adelevance-8185,

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.


5 |1600 characters needed characters left characters exceeded

Up to 10 attachments (including images) can be used with a maximum of 3.0 MiB each and 30.0 MiB total.

ErlandSommarskog avatar image
0 Votes"
ErlandSommarskog answered

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.

5 |1600 characters needed characters left characters exceeded

Up to 10 attachments (including images) can be used with a maximum of 3.0 MiB each and 30.0 MiB total.