Question

我需要写一个查询sql使用全文返回记录的顺序匹配的单词计数

exmaple: in data base

row 1 = "brown cow" //1 match
row 2 = "lazy dog" //2 matches

用户输入:“只敏捷的棕色狐狸跳过了懒惰的狗”

两个输入将会返回2行

Answer 1

我不要看任何本地。您可能需要创建一个函数比较结果你的搜索字符串,以确定有多少场比赛。

你可以通过免费的文本搜索到的结果,函数将返回一个匹配数。最后,您d匹配计算的结果。

Answer 2

在MS SQL游击的方式你可以这样做:

select * from MATCHTABLE where patindex( % +MATCHCOLUMN+ % , The quick brown fox jumps over the lazy dog )>0

MATCHTABLE哪里和你的两个示例表行,和MATCHCOLUMN列名与文本。

然而,这种方法的性能将不会是全文搜索。

Answer 3

鉴于以下假设:

Your table is already full text enabled
Your table is named "example" and contains 2 columns called "exampleID" and "exampleText" (exampleText is in your fulltext index)
You are able to parse the user input as shown for each example:

parsedUserInput =,快速,棕色狐狸,跳,,,懒惰,狗

我认为你寻找的东西是这样的:

SELECT display_term as term
,document_count as numberOfOccurences 
FROM sys.dm_fts_index_keywords (db_id(),object_id( example ))
WHERE display_term IN({parsedUserInput})

否则可能排名将为你工作…

parsedUserInput =“”或“快速”或“棕色”或“福克斯”或“跳跃”或“”或“”或“懒惰”或“狗”

SELECT 
ranks.rank AS rank
,e.exampleID
FROM example e
INNER JOIN CONTAINSTABLE (  example, exampleText,   **{parsedUserInput}**     ) AS ranks
    ON e.exampleID = ranks.[KEY]
WHERE ranks.rank > 0 -- return only rows that have a rank
order by ranks.rank desc

友情链接