SQL | Search a large string to return key-phrases found in table-CodePudding

I have a table that has about 3000 different key-phrases (small strings). I need a query that takes the input of a large-string (like a large sentence or paragraph) and returns the records of key-phrases that exist inside the large-string.

Simplified example: Table has 5 records: "fishing", "pond", "cloud", "big one", "house".

Large-string input: "I have a pond at my house. I decided to go fishing and caught a big one."

Query outputs (matched records): pond, house, fishing, big one

Thank you, James

I tried a few select statements to no avail. Maybe I need to parse the large-string into some kind of array before making a statement/query?

CodePudding user response：

with small (term) as (
        select 'fishing'
  union select 'pond'
  union select 'cloud'
  union select 'big one'
  union select 'house'
)

select term
from small
where 'I have a pond at my house. I decided to go fishing and caught a big one.' like '%'   term   '%'

You didn't specify which RDBMS. In SQL Server, is the concatenation operator. Your mileage may vary.

CodePudding user response：

You can use the FULLTEXT index. No matter how large your string input is.

Then use

SELECT * FROM `table_name` WHERE MATCH(records) AGAINST('"your large string"' IN BOOLEAN MODE) AS relevance;

for more see these answers: MySQL match() against() - order by relevance and column?

MySQL MATCH() AGAINST() FULLTEXT Index - Achieve Partial String Match Combined with Phrase Match

MySql `MATCH AGAINST` and `LIKE` combination to search for special characters