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

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?

sqlgoogle-bigquery


Solution 1:[1]

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

enter image description here

Solution 2:[2]

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

Sources

This article follows the attribution requirements of Stack Overflow and is licensed under CC BY-SA 3.0.

Source: Stack Overflow

Solution Source
Solution 1 Mikhail Berlyant
Solution 2 Shmiel

Related Questions