跨连接列的全文搜索？

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
搜索jim smith将返回名为Jim Smith的所有联系人
Searching for smith ibm returns all contacts named Smith who work at IBM
搜索smith ibm会返回所有在IBM工作的名为Smith的联系人

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.

但这显然是做史密斯或者ibm;它返回了许多不在IBM工作的史密斯，反之亦然。令人惊讶的是，搜索smith和ibm会产生相同的结果。

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.

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

2 个解决方案

#1

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.

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

#2

Boolean operators are only supported for CONTAINS, not FREETEXT.

布尔运算符仅支持CONTAINS，而不支持FREETEXT。

Try your AND query with CONTAINSTABLE.

尝试使用CONTAINSTABLE进行AND查询。

#1