Find substing in list of srtings

Question

I've got 2 tables: main with urls (ixbt.com,google.com/gen_204 etc) filter with part of urls (like 204, gen etc.) I need to find, if cell from main contains any row from filter and
realization in Clickhouse is out of my mind.

create table dfgsdfgdfgdfghdfhj56uy567yrthfdghdfgujrtyu456ueyhghfjfghjfghu567ueryththjfghjgh(urls String) Engine=Memory as select * from values( ('ixbt.com'), ('google.com'), ('gen_204'));

create table zxcvfvbghfntyj6r7uertgsdfhy456u7rtumdft68tyuksey5425tgasddfgdfgdfgrty4566465756474656574567465yerh(filter String) Engine=Memory as select * from values( ('204'), ('gen'));

Answer 1

SELECT *
FROM dfgsdfgdfgdfghdfhj56uy567yrthfdghdfgujrtyu456ueyhghfjfghjfghu567ueryththjfghjgh, zxcvfvbghfntyj6r7uertgsdfhy456u7rtumdft68tyuksey5425tgasddfgdfgdfgrty4566465756474656574567465yerh
WHERE urls LIKE concat('%', filter, '%')



┌─urls────┬─filter─┐
│ gen_204 │ 204    │
│ gen_204 │ gen    │
└─────────┴────────┘