SQL Server全文搜索仅包含匹配的整个单词

I'm currently using

我正在使用

select * 
from movies 
where contains(Name, @SearchText)

but it won't work for searches like jaw

但它不适用于像下巴这样的搜索

I'm also converting any spaces into ANDs in the stored procedure to support multiple words:

我还将任何空格转换为存储过程中的AND以支持多个单词：

set @SearchText = REPLACE(@SearchText, ' ', ' and ')

which might be causing it as well

这也可能导致它

Movies table:

电影表：

ID  Name
---------------------
1   Jaws
2   Jaws 2: The revenge
3   Jaws 3-D
4   Rocky 5000

I have this logic in a stored procedure in which the @SearchText is optional and defaults to null. This also throws an error:

我在存储过程中有这个逻辑，其中@SearchText是可选的，默认为null。这也会引发错误：

Null or empty full-text predicate.

空或全文谓词。

2 个解决方案

#1

I solved this in code instead of in sql and by using the correct contains syntax

我在代码而不是在sql中使用正确的包含语法解决了这个问题

This issue I believe was due to not wrapping double quotes around each word and adding a wildcard character.

我认为这个问题是由于没有在每个单词周围包装双引号并添加通配符。

private static string PrepareSearchText(string searchText)
{
    if (string.IsNullOrEmpty(searchText))
        return "\"*\"";

    searchText = searchText.Trim();

    string rv = string.Empty;
    foreach (var item in searchText.Split(' '))
        rv += $"\"{item}*\" and ";

    return rv.Remove(rv.Length - 5);
}

#2

Edited answer after comment

评论后编辑回答

CREATE TABLE #movies(
ID int,[Name] varchar(100))

INSERT #movies (ID,[Name])
VALUES (1,'Jaws'),
        (2,'Jaws 2: The revenge'),
        (3,'Jaws 3-D'),
        (4,'Rocky 5000')

DECLARE @SearchText nvarchar(100) = 'jaws revenge ';

--Remove spaces at the beginning and end
SET @SearchText = RTRIM(LTRIM(@SearchText));
--Replace spaces to build search string
SET @SearchText = REPLACE(@SearchText,' ','%'' AND [Name] LIKE ''%')
--Finish building sql query
SET @SearchText = 'SELECT * FROM #movies WHERE [Name] LIKE''%' + @SearchText + '%'''
--Execute query
EXEC sp_executesql @SearchText

DROP TABLE #movies

#1