连续列中的全文搜索？ [英] Full-text search across concatenated columns?

问题描述

我是新来的自由文本搜索，所以请原谅这个新手问题。假设我有以下全文索引：

 在联系人上创建全文索引（
 FirstName，
姓氏，
组织
）
钥匙索引PK_Contacts_ContactID 
 Go 
  

我想对连接的所有三列进行自由文本搜索

 名字+''+ LastName +''+组织
  

例如

• 搜索 jim smith 返回所有名为Jim Smith的联系人


• 搜索 smith ibm 返回所有在IBM工作的名为Smith的联系人

这似乎是一种相当常见的情况。我希望这能起作用：

 选择c.FirstName，c.LastName，c.Organization，ft.Rank $ b $ FreeTextTable中的$ b（Contacts，*，'smith ibm'）ft 
左加入联系人c上的英尺[键] = c.ContactID 
按ft.Rank排序Desc  

但这显然是在做 smith或ibm ;它会返回许多不在IBM工作的Smiths，反之亦然。令人惊讶的是，搜索 smith和ibm 会得到相同的结果。

这就是我想要的...

 选择c。 FirstName，c.LastName，c.Organization 
来自联系人c 
其中Contains（*，'smith'）和Contains（*，'ibm'）
  

...但是我无法参数化来自用户的查询 - 我必须自己将搜索字符串分解为单词并组装SQL在飞行中，这是丑陋的和不安全的。

解决方案

我常用的方法是创建一个搜索视图或计算列（使用触发器），将所有这些值到一个单一的领域。

我做的另一件事是使用全文搜索引擎 - 例如Lucene / Solr。

I'm new to free-text search, so pardon the newbie question. Suppose I have the following full-text index:

Create FullText Index on Contacts(
    FirstName,
    LastName,
    Organization
)
Key Index PK_Contacts_ContactID
Go

I want to do a freetext search against all three columns concatenated

FirstName + ' ' + LastName + ' ' + Organization

So that for example

• Searching for jim smith returns all contacts named Jim Smith
• Searching for smith ibm returns all contacts named Smith who work at IBM

This seems like it would be a fairly common scenario. I would have expected this to work:

Select c.FirstName, c.LastName, c.Organization, ft.Rank
from FreeTextTable(Contacts, *, 'smith ibm') ft
Left Join Contacts c on ft.[Key]=c.ContactID
Order by ft.Rank Desc

but this is apparently doing smith OR ibm; it returns a lot of Smiths who don't work at IBM and vice versa. Surprisingly, searching for smith AND ibm yields identical results.

This does what I want...

Select c.FirstName, c.LastName, c.Organization
from Contacts c 
where Contains(*, 'smith') and Contains(*, 'ibm')

...but then I can't parameterize queries coming from the user -- I would have to break up the search string into words myself and assemble the SQL on the fly, which is ugly and unsafe.

解决方案

The usual approach I take is to create a search view or calculated column (using a trigger) that puts all of those values into a single field.

The other thing I do is to use a full-text search engine- such as Lucene/Solr.

这篇关于连续列中的全文搜索？的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！