I would like to know if there is a way to get the words that were matched from a full-text search using FREETEXT.
For example:
SELECT *
FROM table
WHERE FREETEXT(column_name, 'search term')
I'd like to be able to see which parts of the text in column_name triggered a match with 'search term' since FREETEXT returns records where the searched column are more than just the search term pattern matches as stated here in the FREETEXT/FREETEXTTABLE section.
I know that using FREETEXTTABLE can return the rank and key but I need to know which actual terms triggered a match if it is possible.
Thanks
Disclaimer, I haven't done this -- I'm going from documentation. I'm not sure this gets you what you want, but you can see how the search is tokenized:
from here
After you apply a given word breaker, thesaurus, and stoplist combination in a query, you can see how Full-Text Search tokenizes the results by using the sys.dm_fts_parser dynamic management view. For more information, see sys.dm_fts_parser (Transact-SQL).
I'm guessing if you want to know which part (token) caused the match -- you'll likely need to run the search on the individual tokens and compare the result sets to see which token or tokens (search term/s) caused the given match.