SQL问题：使用CONTAINS（）不起作用，但LIKE工作正常 [英] SQL Problem: Using CONTAINS() doesn't work, but LIKE works fine

问题描述

我在SQL Server数据库中有一个Products表，我必须对使用全文索引的旧式存储过程进行故障排除。为了我们的目的，假设Products表中有两个字段ID，关键字。关键字字段填入以下内容：

ROLAND SA-300这款Roland SA-300在MINT条件下价格为
！

当我运行以下语句时，我可以检索记录：

  SELECT * FROM Products WHERE关键字LIKE'％SA-300％'
  

然而，当我运行以下任何语句时，我得到零结果：

  SELECT * FROM产品Where CONTAINS （关键字，'SA-300'）
选择*从产品哪里（关键字，'SA-300'）
选择*从产品哪里CONTAINS（关键字，'SA-300''）
 SELECT * FROM产品WHERE CONTAINS（关键字，'* SA-300 *''）
  

但是我知道CONTAINS（）函数正在工作，因为当我运行这些函数时，我得到了结果：
$ b

  SELECT * FROM产品Where CONTAINS（关键字，'Roland'）
 SELECT * FROM Produc （关键词，'罗兰'）
选择*从产品哪里包含（关键词，'罗兰''）
选择*从产品哪里包含（关键词，'*罗兰*'） 
 SELECT * FROM Products WHERE CONTAINS（关键字，'Roland'）
  

为什么CONTAINS（）函数不适用于'SA-300'这个术语。

解决方案

两个想法：

（1）连字符可能被当作单词中断
这些返回是什么？

 <$ c （关键字，'SA'）
 SELECT * FROM产品WHERE CONTAINS（关键字，'SA 300'）
 SELECT * FROM产品Where CONTAINS（关键字，'SA300' ）
  

请参阅。

（2）有你尝试重建你的全文索引？这可能已过时。

I have a Products table in a SQL Server database and I am having to troubleshoot a legacy stored procedure that uses Full-Text indexing. For our purposes here, lets suppose that the Products table has two fields ID, Keywords. And the Keywords field is populated with the following:

ROLAND SA-300 This Roland SA-300 is in MINT condition!

When I run the following statement, I am able to retrieve the record:

SELECT * FROM Products WHERE Keywords LIKE '%SA-300%'

However, when I run any of the following statements I get zero results:

SELECT * FROM Products WHERE CONTAINS(Keywords, ' SA-300 ')
SELECT * FROM Products WHERE CONTAINS(Keywords, 'SA-300')
SELECT * FROM Products WHERE CONTAINS(Keywords, '"SA-300"')
SELECT * FROM Products WHERE CONTAINS(Keywords, '"*SA-300*"')

But I know that the CONTAINS() function is working because I get results when I run any of these:

SELECT * FROM Products WHERE CONTAINS(Keywords, ' Roland ')
SELECT * FROM Products WHERE CONTAINS(Keywords, 'Roland')
SELECT * FROM Products WHERE CONTAINS(Keywords, '"Roland"')
SELECT * FROM Products WHERE CONTAINS(Keywords, '"*Roland*"')
SELECT * FROM Products WHERE CONTAINS(Keywords, 'Roland')

I need to figure out why the CONTAINS() function isn't working on the 'SA-300' term. I am new to Full-text indexes, so any help is appreciated.

解决方案

Two thoughts:

(1) The hyphen might be treated as a word break What do these return?

SELECT * FROM Products WHERE CONTAINS(Keywords, 'SA')
SELECT * FROM Products WHERE CONTAINS(Keywords, 'SA 300')
SELECT * FROM Products WHERE CONTAINS(Keywords, 'SA300')

See this other question.

(2) Have you tried rebuilding your full-text index? It is possible it is out of date.

这篇关于SQL问题：使用CONTAINS（）不起作用，但LIKE工作正常的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！