問題描述

嘗試做一個小的存儲過程而不需要為此添加自由文本索引(SQL Server 2008)

trying to do a small stored procedure without needing to add freetext indexing just for this (SQL Server 2008)

基本上，我想查找某個字段包含參數(shù)中所有單詞的所有記錄.

Basically, I want to find all records where a certain field contains all the words from a parameter.

因此，如果在字段中我有這是一個測試字段"，并且我的 SP 的參數(shù)是這個測試字段"，它將返回它，就像參數(shù)是字段這個測試"一樣.

So if in the field I have "This is a test field", and the parameter to my SP would be "this test field" it would return it, as it would if the parameter was "field this test".

表很小(4000條)記錄，負(fù)載會很低，所以效率不是什么大問題.現(xiàn)在我能想到的唯一解決方案是用表值函數(shù)拆分兩個字符串并從那里開始.

The table is very small (4000) record and load will be low, so efficiency is not a big deal. Right now the only solution i can think of is to split both strings with table valued function and go from there.

有更簡單的想法嗎?

謝謝！

推薦答案

這是一個使用遞歸 CTE 的解決方案.這實(shí)際上使用了兩個單獨(dú)的遞歸.第一個將字符串拆分為標(biāo)記，第二個使用每個標(biāo)記遞歸過濾記錄.

Here is a solution using recursive CTEs. This actually uses two separate recursions. The first one splits the strings into tokens and the second one recursively filters the records using each token.

declare     
    @searchString varchar(max),
    @delimiter char;

select 
@searchString  = 'This is a test field'
,@delimiter = ' '

declare @tokens table(pos int, string varchar(max))

 ;WITH Tokens(pos, start, stop) AS (
      SELECT 1, 1, CONVERT(int, CHARINDEX(@delimiter, @searchString))
      UNION ALL
      SELECT pos + 1, stop + 1, CONVERT(int, CHARINDEX(@delimiter, @searchString, stop + 1))
      FROM Tokens
      WHERE stop > 0
    )
    INSERT INTO @tokens
    SELECT pos,
      SUBSTRING(@searchString, start, CASE WHEN stop > 0 THEN stop-start ELSE 512 END) AS string
    FROM Tokens
    OPTION (MAXRECURSION 25000) ;

;with filter(ind, myfield) as (
    select  1,myfield from mytable where myfield like '%'+(select string from @tokens where pos = 1)+'%'    
    union all
    select  ind + 1, myfield from filter where myfield like '%'+(select string from @tokens where pos = ind + 1)+'%'    
    )

    select * from filter where ind = (select COUNT(1) from @tokens)

這花了我大約 15 秒來搜索包含 10k 條記錄的表格以查找搜索字符串 'this is a test field'..(字符串中的單詞越多，花費(fèi)的時(shí)間越長..)

This took me about 15 seconds to search a table of 10k records for the search string 'this is a test field'.. (the more words in the string, the longer it takes.. )

編輯
如果您想要模糊搜索，即即使沒有完全匹配也返回緊密匹配的結(jié)果，您可以將查詢中的最后一行修改為 -
select * from (select max(ind) as ind, myfield from filter group by myfield) t order by ind desc

'ind' 會為您提供在 myfield 中找到的搜索字符串中的單詞數(shù).

'ind' would give you the number of words from the search string found in myfield.

這篇關(guān)于搜索包含另一個字符串中所有單詞的 varchar 字段的文章就介紹到這了，希望我們推薦的答案對大家有所幫助，也希望大家多多支持html5模板網(wǎng)！