How to check if column values contain any of array elements in clickhouse?

Question

Let's say we have a table with column "titles" like this:

title
The Colour of Magic
The Light Fantastic
Equal Rites
Wyrd Sisters
Reaper Man

and filtering words like this

filter_words = ['Man', 'Sisters']

The main idea I came up to is to convert filter_words from array to table like this

select arrayJoin(['Man', 'Sisters']) as filter_word

and join on some condition. I've tried this condition but got an exception that condition is not supported. what is the correct way to filter column by string part?

JOIN filter_words
    ON position(not_filtered.title, filter_words.filter_word) > 0

JOIN filter_words
    ON '%' + filter_words.filter_word + '%' like not_filtered.title

Answer 1

You may split the initial string to array of tokens and use a handy hasAny function to check if two arrays have intersection by some elements.

WITH ['Man', 'Sisters'] AS filter_words
SELECT title
FROM VALUES 
(
  'title String'
, 'The Colour of Magic'
, 'The Light Fantastic'
, 'Equal Rites'
, 'Wyrd Sisters'
, 'Reaper Man'
) t
WHERE hasAny(splitByWhitespace(t.title), filter_words)

The result is:

title
Wyrd Sisters
Reaper Man