SQL Server全文搜索使用另一列作为搜索词 [英] SQL Server fulltext search using another column as a search term

问题描述

我试图在SQL Server 2016中对另一个字段的结果进行全文搜索

当我查询文本' CSN'，但是如何将'CSN'更改为列中的所有结果，而不仅仅是一个文本结果？

例如，工作表栏中的所有项目都等于另一个表中名为SX的列中的所有结果？

  SELECT * 
 FROM dbo.sheet 
 WHERE CONTAINS（sheet，'CSN'）
  

解决方案

据我所知，无法在中使用列引用作为搜索词， CONTAINS 。但是，如果您设法将搜索字词放入变量中，那么 CONTAINS 会接受它：

  DECLARE @searchTerm VARCHAR（10）='CSN'
 SELECT * 
 FROM dbo.sheet 
 WHERE CONTAINS（sheet，@searchTerm）
  

OR

  DECLARE @searchTerm VARCHAR 10）=''
 SELECT TOP 1 @ searchTerm = SX FROM table2 WHERE ... 
 
 SELECT * 
 FROM dbo.sheet 
 WHERE CONTAINS（sheet，@） searchTerm）
  

另一种方法可能是创建一个用户定义的函数，该函数将返回一个包含搜索结果的表格，将搜索词作为参数传递给它。在函数内部，您将调用 SELECT ... WHERE CONTAINS（sheet，@funcParam）。然后您必须使用 CROSS APPLY 来加入表值函数的结果。

I am trying to base a full text search in SQL server 2016 on the results of another field

I am able to get results when I query for the text 'CSN', but how to I change the 'CSN' to all of the results in a column, not just one text result?

For example all of the items in the sheet column that equal all of the results in a column called SX in another table?

SELECT *
FROM dbo.sheet
WHERE CONTAINS(sheet, 'CSN')

解决方案

As far as I know there is no way to use column reference as a search term in CONTAINS. But if you manage to put your search term into variable then CONTAINS will accept it:

DECLARE @searchTerm VARCHAR(10) = 'CSN'
SELECT *
FROM dbo.sheet
WHERE CONTAINS(sheet, @searchTerm)

OR

DECLARE @searchTerm VARCHAR(10) = ''
SELECT TOP 1 @searchTerm=SX FROM table2 WHERE ...

SELECT *
FROM dbo.sheet
WHERE CONTAINS(sheet, @searchTerm)

Another approach may be to create a user defined function which would return a table with search results and pass the search term as a param to it. Inside the function you will call SELECT ... WHERE CONTAINS(sheet, @funcParam). You will then have to use CROSS APPLY to join to the result of the table valued function.

这篇关于SQL Server全文搜索使用另一列作为搜索词的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！