如何在FORMSOF INFLECTIONAL全文搜索中获得匹配的位置？

问题描述

我在MS SQL Server的全文索引引擎中使用CONTAINSTABLE查询在文本列中进行搜索;例如：

  SELECT * 
 FROM MyTable 
 INNER MERGE JOIN CONTAINSTABLE（MyTable，sDescription，'FORMSOF（ INFLECTIONAL，brains）'）
 AS TBL1 ON TBL1。[key] = MyTable.ixKey 
  

这在寻找包含大脑（如大脑，脑筋等词）描述的行方面做得非常出色。但是，当我将这些结果显示给用户时，我想突出显示与其查询相匹配的单词（就像Google一样）。但我不能只在结果中查找搜索词：如果结果中包含大脑，我显然不能突出显示大脑。

可以SQL服务器告诉我在全文匹配发生的列（无论是单词还是字符）中的哪个位置？或者，我可以手动运行词干分析器来获取搜索词的所有形式吗？然后，我可以单独突出显示每个单词。

解决方案

SQL Server 2008包含一个函数，用于获取单词的变形形式或短语，使用全文引擎的解析器：。

  SELECT display_term，source_term，occurrence FROM sys.dm_fts_parser（'FORMSOF（INFLECTIONAL，brains）'，1033，0，0）
  

获得一个表，如：

  display_term | source_term |发生
 --------------------------------------- 
 brain |大脑| 1 
大脑|大脑| 1 
 brained |大脑| 1 
  

（使用查询短语有更多的工作，因为它可以分开地描述每个单词，但它是并不难。）

现在我可以突出显示任何发生变化的任何形式。这比SQL Server刚刚告诉我FTS匹配的地方要多一点，但它会做。

I'm using a CONTAINSTABLE query with MS SQL Server's full-text indexing engine to search within a textual column; e.g.:

SELECT * 
FROM MyTable
INNER MERGE JOIN CONTAINSTABLE(MyTable, sDescription, 'FORMSOF(INFLECTIONAL, "brains")')
    AS TBL1 ON TBL1.[key]=MyTable.ixKey

This does a great job of finding rows with a description including a word like "brains" (e.g. "brain", "brained"). However, when I display these results to the user, I'd like to highlight the word that matched their query (just like Google). But I can't just look for the search term in the results: if the result contains "brain", I obviously can't highlight "brains".

Can SQL Server tell me where in the column (either the word or character) the full-text match occurs? Alternatively, can I manually run the stemmer to get all the forms of the search term? I could highlight each of those individually, then.

解决方案

SQL Server 2008 includes a function to get the inflected forms of a word or phrase, using the full-text engine's parser: sys.dm_fts_parser.

SELECT display_term, source_term, occurrence FROM sys.dm_fts_parser('FORMSOF(INFLECTIONAL, "brains")', 1033, 0, 0)

gets a table like:

display_term | source_term | occurrence
---------------------------------------
brain        | brains      | 1
brains       | brains      | 1
brained      | brained     | 1

(Working with query phrases is a bit more work, as it inflects each word separately, but it's not too hard to put things back together.)

Now I can just highlight any occurrence of any of the inflected forms. It's a bit more work than if SQL Server just told me where the FTS matches are, but it'll do.

这篇关于如何在FORMSOF INFLECTIONAL全文搜索中获得匹配的位置？的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持！