我的Sql Server存储过程中的搜索查询问题

时间:2021-01-04 23:35:53

I have the following stored proc :-

我有以下存储过程: -

SELECT Id, Name
FROM FooBars
WHERE CONTAINS(Name, 'FORMSOF(Tesaurus, @query)')

Works fine when there is one word i the query: eg. foo* But it fails when I want to have more than one word which i'm trying to look for. eg. foo* bar* (this means any rows that have words that start with foo and start with bar).

当查询中有一个单词时工作正常:例如。 foo *但是当我想拥有一个我正在寻找的单词时,它失败了。例如。 foo * bar *(这意味着任何包含以foo开头并以bar开头的单词的行)。

What format does my @query argument need to look like, to enable multiple words for a Full Text Search with a Thesaurus?

我的@query参数需要采用什么格式才能使用同义词库为全文搜索启用多个单词?

1 个解决方案

#1


As far as I know, if you want to search for two or more expressions, you need to concatenate those with either AND, OR or NEAR, something like this (straight from Books Online):

据我所知,如果你想搜索两个或更多的表达式,你需要将它们连接到AND,OR或NEAR,就像这样(直接来自联机丛书):

USE AdventureWorks;
GO

SELECT Name
FROM Production.Product
WHERE CONTAINS(Name, '"chain*" OR "full*"');
GO

SELECT Description
FROM Production.ProductDescription
WHERE CONTAINS(Description, 'bike NEAR performance');
GO

SELECT Description
FROM Production.ProductDescription
WHERE ProductDescriptionID <> 5 AND
CONTAINS(Description, ' Aluminum AND spindle');

If and how that would work together with your FORMSOF(...) expression is unclear to me - but I'm sure you could quickly try that, no?

如果以及如何与你的FORMSOF(...)表达一起工作我不清楚 - 但我相信你可以快速尝试,不是吗?

SELECT Id, Name
FROM FooBars
WHERE CONTAINS(Name, 'FORMSOF(THESAURUS, "foo*")'
    OR 'FORMSOF(THESAURUS, "bar*")')

Also make sure to spell "thesaurus" correctly in your FORMSOF () expression! :-)

还要确保在FORMSOF()表达式中正确拼写“同义词库”! :-)

Marc

#1


As far as I know, if you want to search for two or more expressions, you need to concatenate those with either AND, OR or NEAR, something like this (straight from Books Online):

据我所知,如果你想搜索两个或更多的表达式,你需要将它们连接到AND,OR或NEAR,就像这样(直接来自联机丛书):

USE AdventureWorks;
GO

SELECT Name
FROM Production.Product
WHERE CONTAINS(Name, '"chain*" OR "full*"');
GO

SELECT Description
FROM Production.ProductDescription
WHERE CONTAINS(Description, 'bike NEAR performance');
GO

SELECT Description
FROM Production.ProductDescription
WHERE ProductDescriptionID <> 5 AND
CONTAINS(Description, ' Aluminum AND spindle');

If and how that would work together with your FORMSOF(...) expression is unclear to me - but I'm sure you could quickly try that, no?

如果以及如何与你的FORMSOF(...)表达一起工作我不清楚 - 但我相信你可以快速尝试,不是吗?

SELECT Id, Name
FROM FooBars
WHERE CONTAINS(Name, 'FORMSOF(THESAURUS, "foo*")'
    OR 'FORMSOF(THESAURUS, "bar*")')

Also make sure to spell "thesaurus" correctly in your FORMSOF () expression! :-)

还要确保在FORMSOF()表达式中正确拼写“同义词库”! :-)

Marc