question

siugoalie78 avatar image
siugoalie78 asked

Full Text Search, return other common words

I have a requirement from a customer to build a query that searches all columns of a table using a Full text index for a specific phrase. That part is no problem. But, then the user also wants to be able to return other high-frequency words that are found in the same row. For example, a row may contain the sentence "The quick brown fox jumped over the lazy brown dog." The user searches for "fox." I will pass that into a query using the "contains" clause, and the row will be returned. Lets say Dog, quick, and lazy are also common words in the table. The user would like to see a return that shows something like: "The row also contains 'Dog, 'quick,' and 'lazy'." Is there a good method to scan the row returned for high frequency words, and then return those as well?
full-text
10 |1200

Up to 2 attachments (including images) can be used with a maximum of 512.0 KiB each and 1.0 MiB total.

1 Answer

·
smooth1x avatar image
smooth1x answered
Depends which version of SQL Server, with 2008+ check: sys.dm_fts_index_keywords( DB_ID('database_name'), OBJECT_ID('table_name') ) - display_term,document_count sys.dm_fts_index_keywords_by_document (DB_ID('database_name'),OBJECT_ID('table_name')) - display_term,document_id,occurrence_count https://technet.microsoft.com/en-us/library/ms174971.aspx
1 comment
10 |1200

Up to 2 attachments (including images) can be used with a maximum of 512.0 KiB each and 1.0 MiB total.

Thanks - I had looked a bit at those views, and figured the answer must lie in there. What I'll probably end up doing is building an SSIS job to populate a table with like the top 10K from those that actually start with an Alpha character, and use that to compare against the results from my result set.
0 Likes 0 ·

Write an Answer

Hint: Notify or tag a user in this post by typing @username.

Up to 2 attachments (including images) can be used with a maximum of 512.0 KiB each and 1.0 MiB total.