SQL Server FullTextSearch FREETEXT does not return expected results

Question

I have the following sql server query

SELECT * 
FROM [dbo].Client
WHERE FullName LIKE '%Lind%'

Which returns the following row:

When I run the following Full Text Search query that row is not returned

SELECT * 
FROM [dbo].Client
WHERE FREETEXT(FullName, 'Lind')

Why does this happen? FREETEXT is supposed to match and return this row.

Updates to fulltext indexes happen asynchronously. If you've only just inserted that row, or if the index updates are configured to be run manually, that row may not have been captured in the fulltext catalog yet. — AlwaysLearning, Jul 27 '20 at 09:40
@pantonis In case you were unaware: _"Alternatively, you can keep your full-text indexes up-to-date manually, or at specified scheduled intervals. Populating a full-text index can be time-consuming and resource-intensive. Therefore, index updating is usually performed as an asynchronous process that runs in the background and keeps the full-text index up to date after modifications in the base table."_ REF: [Get Started with Full-Text Search](https://docs.microsoft.com/en-us/sql/relational-databases/search/get-started-with-full-text-search) Making it synchronous slows inserts and updates. — AlwaysLearning, Jul 27 '20 at 22:45

score 0 · Answer 1 · answered Jul 27 '20 at 12:12

0

SELECT * 
FROM [dbo].Client
WHERE CONTAINS(FullName,'"Lind*"') ; --prefix of word: prefix+asterisk in double quotes

answered Jul 27 '20 at 12:12

lptr

Question is about FREETEXT. I know that CONTAINS works with asterisk. Question is why FREETEXT does not work? From what I read FREETEXT should return the result. – pantonis Jul 27 '20 at 13:55
https://learn.microsoft.com/en-us/sql/t-sql/queries/freetext-transact-sql : `freetext_string is wordbroken, stemmed, and passed through the thesaurus.` one could not expect the Lindgren lastname to appear in any thesaurus (stemming of Lindgren should be Lindgren too(?) hence freetext for Lind returns no matches) – lptr Jul 27 '20 at 14:30
@Iptr thanks. What about when I want to search for prefix. e.g ```Sadlind```. The above query does not work if you move the asterisk in front ```WHERE CONTAINS(FullName,'"*Lind*"'``` – pantonis Jul 28 '20 at 06:28
CONTAINS can search for the prefix of a word or phrase but not for part of a word. Sadlind cannot be matched with a fulltext search for Lind. – lptr Jul 28 '20 at 16:22
so there is not any functionality in SQL Server FullText Search to use a kind of ```"LIKE"``` operator? – pantonis Jul 29 '20 at 07:35

1 Answers1