Search code examples
sqlsql-servert-sqlfull-text-search

SQL Server fulltext search using another column as a search term

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')
Solution

  • 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.