Filter with like in BigQuery where the pattern are in an array

Question

I would like to filter a column in bigquery where the LIKE should match multiple patterns. Something like:

SELECT description
FROM mytable
WHERE 
    mycol LIKE "%window%"
    OR mycol LIKE "%door%"
    OR mycol LIKE "%carpet%"

but I would like it to be

SELECT description
FROM mytable
WHERE 
    mycol LIKE IN ["%window%", "%door%", "%carpet%"]

Ideally the patterns ("window", "door", "carpet",...) are stored in another table (so they are dynamically generated).

What is the best way to do that?

Answer 1

Are you perhaps looking for Quantified Like Operator.

See sample here using LIKE ANY:

WITH Words AS
 (SELECT 'Intend with clarity.' as value UNION ALL
  SELECT 'Secure with intention.' UNION ALL
  SELECT 'Clarity and security.')
SELECT * FROM Words WHERE value LIKE ANY ('Intend%', '%intention%');

/*------------------------+
 | value                  |
 +------------------------+
 | Intend with clarity.   |
 | Secure with intention. |
 +------------------------*/