SQL: how to check if at least one keyword from a keyword list exists in a string-CodePudding

I have a table with the following schema,

id   data_string
1    I have a pen.
2    Monday is not good.
3    I love Friday.
...  ...

And I also have a list of keywords ['pen', 'love', 'apple'].

I want to write a SQL script to find if any of the keyword in my list of keywords can be found in the data_string column of the table.

And I'd like the output of the script be like

id   keyword_exist
1    true 
2    false
3    true
...  ...

Is this doable with SQL?

CodePudding user response：

Consider below

with keywords as (
  select ['pen', 'love', 'apple'] list
)
select t.*, regexp_contains(data_string, r'' || pattern) keyword_exist
from your_table t, 
( select array_to_string(list, '|') pattern
  from keywords
)

if applied to sample data in your question - output is

CodePudding user response：

Does this work?

SELECT ID, data_string, CASE WHEN data_string LIKE '% pen %' OR data_string LIKE '% love %' OR data_string LIKE '% apple %' THEN 'True' ELSE 'False' END AS keyword_exist FROM table_name