英文:
Using SQL CONTAINS with multiple words inside a variable
问题
我正在尝试使用带有变量的SQL CONTAINS函数。通常，我可以按以下方式使用CONTAINS：
WHERE CONTAINS(tablename, '"some text to search for"')
然而，我无法弄清如何在该函数内插入一个变量，以便允许我搜索多个词语。
WHERE CONTAINS(tablename, @Keyword)
仅允许搜索一个词，并且如果传递多个词，将引发异常。
Msg 7630，Level 15，State 3，Procedure dbo.bp_SearchCategoryByKeyword，Line 11 [Batch Start Line 0] Syntax error near 'this' in the full-text search condition
英文:
I'm trying to use SQL CONTAINS function with variables. Usually, I can use CONTAINS the following way:
WHERE CONTAINS(tablename, '"some text to search for"')
However, I can't figure out how to insert a variable inside that function that would allow me to search with multiple words.
WHERE CONTAINS(tablename, @Keyword)
only allows to search for one word and will throw an exception if multiple words are passed to it.
> Msg 7630, Level 15, State 3, Procedure dbo.bp_SearchCategoryByKeyword,
> Line 11 [Batch Start Line 0] Syntax error near 'this' in the full-text
> search condition
答案1
得分: 2
这部分的翻译如下：
我刚刚尝试了这个，并且它按预期工作了：
DECLARE @SearchTerms varchar(8000) = 'file AND read';
SELECT MessageID, Description
FROM dbo.Messages
WHERE CONTAINS(Description, @SearchTerms)
ORDER BY MessageID;
这也有效：
DECLARE @SearchTerms varchar(8000) = '"file" AND "read"';
SELECT MessageID,Description
FROM dbo.Messages
WHERE CONTAINS(Description, @SearchTerms)
ORDER BY MessageID;
英文:
I just tried this and it worked as expected:
DECLARE @SearchTerms varchar(8000) = 'file AND read';
SELECT MessageID, Description
FROM dbo.Messages
WHERE CONTAINS(Description, @SearchTerms)
ORDER BY MessageID;
This also worked:
DECLARE @SearchTerms varchar(8000) = '"file" AND "read"';
SELECT MessageID,Description
FROM dbo.Messages
WHERE CONTAINS(Description, @SearchTerms)
ORDER BY MessageID;
答案2
得分: 0
这是一个有点巧妙的方法，但是从第一个变量创建第二个变量，用双引号括起第一个变量，将起作用：
DECLARE @p_searchtermTitle NVARCHAR(255) = ''""''
DECLARE @p_searchtermTitle2 NVARCHAR(255) = ''""''
SET @p_searchtermTitle = 'The Birth of the World'
SET @p_searchtermTitle2 = ''"'' + @p_searchtermTitle + ''"''
SELECT @p_searchtermTitle, @p_searchtermTitle2, OT.Title
FROM ObjTitles OT
WHERE CONTAINS(OT.Title, @p_searchtermTitle2)
英文:
It is a bit of a hack, but creating a second variable from the first, with double quotes around the first var, will work:
DECLARE @p_searchtermTitle NVARCHAR(255) = '""'
DECLARE @p_searchtermTitle2 NVARCHAR(255) = '""'
SET @p_searchtermTitle = 'The Birth of the World'
SET @p_searchtermTitle2 = '"' + @p_searchtermTitle + '"'
SELECT @p_searchtermTitle, @p_searchtermTitle2, OT.Title
FROM ObjTitles OT
WHERE CONTAINS(OT.Title, @p_searchtermTitle2)
通过集体智慧和协作来改善编程学习和解决问题的方式。致力于成为全球开发者共同参与的知识库，让每个人都能够通过互相帮助和分享经验来进步。
评论