sql-server - 即使使用 u 作为关键字搜索，SQL CONTAINS 函数也不显示带有“under”一词的记录

Question

我无法使用 SQL CONTAINS函数显示包含单词“under”的记录。我为此阅读了 Microsoft 文档，并基于那里使用 AdventureWorks DB 的示例，结果集与预期的一样，但是当应用相同的示例来搜索具有单词“ under ”的记录时会遇到困难。

这是我正在处理的脚本，您也可以执行它来模拟场景：

--Creating table
CREATE TABLE MyTable
(
   ID INT NOT NULL IDENTITY(1,1),
   [Description] NVARCHAR(200) NULL
   CONSTRAINT PK_ID PRIMARY KEY CLUSTERED (ID)
)

--Populating table
INSERT INTO MyTable
SELECT 'Testing'
UNION SELECT 'This is it'
UNION SELECT 'Under the name of whoever'
UNION SELECT 'I undergo surgery'
UNION SELECT 'Under Armour'
UNION SELECT 'Frequent under the table'
UNION SELECT 'I am underpaid too'
UNION SELECT 'Anything under the sun'
UNION SELECT 'What is the matter peanut butter'
UNION SELECT 'Underline'
UNION SELECT 'Chainring Bolts'
UNION SELECT 'Chaining Nut'
UNION SELECT 'Chainring'
UNION SELECT 'Chain Stays'
UNION SELECT 'Chain'
UNION SELECT 'Dummy Chain'
UNION SELECT 'Land Down Under'
UNION SELECT 'Many Underlings'
UNION SELECT 'Only Undo'

--One time setup to enable fulltext search
EXEC sp_fulltext_database 'enable'
GO
CREATE FULLTEXT CATALOG FTCSearch
GO
CREATE FULLTEXT INDEX ON MyTable([Description])
   KEY INDEX PK_ID ON FTCSearch
GO

询问：

--Query showing phrases that have c prefix
SELECT * FROM MyTable
WHERE CONTAINS([Description], ' "c*" ')
--Getting the expected result

--Query showing phrases that have u prefix
SELECT * FROM MyTable
WHERE CONTAINS([Description], ' "under*" ')
--Not getting expected result: Missing phrases that contains 'under' word
--Missing: Under the name of whoever
--Missing: Under Armour
--Missing: Frequent under the table
--Missing: Anything under the sun
--Missing: Land Down Under
--Missing: Under the name of whoever

标签： sql-servertsqlfull-text-search

Answer 1

我遇到过同样的问题。我做了以下查询，然后它开始为我工作。

ALTER FULLTEXT INDEX ON MyTable SET STOPLIST = OFF

DECLARE @SearchWord varchar(200)='Under'

SELECT * FROM MyTable
WHERE CONTAINS(Description, @SearchWord);