SQL Server Full-Text Search against Table of Phrases

SteveMaxK 1 Reputation point
2022-06-20T00:57:04.067+00:00

Hi,
I am able to use the following SQL Server Transact-SQL query to search for a phrase in a column; SELECT * FROM TABLE WHERE CONTAINS(COLUMN,' "Phrase" ').

What I would like to do is search for Phrases that are in a column of text in a table. I have a table called Terminology that has a column called Diagnosis. I would like to be able to search other tables text for phrases in my Terminology table.

I am aware there is a SSIS solution using Term Extraction and Term Lookup, I am looking if there is a solution within Transact-SQL.

Any assistance you can provide will be greatly appreciated.

Transact-SQL
Transact-SQL
A Microsoft extension to the ANSI SQL language that includes procedural programming, local variables, and various support functions.
4,689 questions
{count} votes

2 answers

Sort by: Most helpful
  1. Bert Zhou-msft 3,436 Reputation points
    2022-06-20T02:26:03.79+00:00

    Hi,@SteveMaxK

    I think this is similar to the where function of google full-text search , to be precise, like googleTosql class, refer to this document, it may be helpful to you.

    Best regards,
    Bert Zhou

    0 comments No comments

  2. Erland Sommarskog 116.5K Reputation points MVP
    2022-06-20T22:05:01.057+00:00

    If I understand you correctly, you want to do something similar to:

       SELECT * FROM Terminology t  
       WHERE EXISTS (SELECT * FROM YourTable Y   
                         WHERE CONTAINS(Y.YourColumn, t.Diagnosis)  
    

    Unfortunately, CONTAINS does not accept a column for input, so you would have to generate a query for each row you want to search for. Or a pattern with all of the words.

    0 comments No comments

Your answer

Answers can be marked as Accepted Answers by the question author, which helps users to know the answer solved the author's problem.