CODEWITHSUNDEEP
google-bigquerysql-like
Hamza
Hamza

Reputation: 6025

How to check for multiple patterns in Google BigQuery SQL? (LIKE + IN)

So I have to search for a bunch of names in a BigQuery table which I collect periodically in another dataset. The dataset is too large at this point containing almost ~60k names and I no longer can do

SELECT * FROM base.table WHERE name LIKE '%name1%' OR name LIKE '%name2%.....

As I tried it using a python script with:

SELECT * FROM base.table WHERE name LIKE({' OR '.join([f'ulv4.full_name LIKE %{name}%' for name in names])})

But the character limit for query exceeds for this many names. I tried looking at solutions like this and other answers to the same question but no answer seems to work for BigQuery Standard SQL. Any help in this regard is highly appreciated.

Upvotes: 0

Views: 1919

Answers (1)

Tim Biegeleisen
Tim Biegeleisen

Reputation: 520928

You should keep the names in another table and then join to it:

SELECT *
FROM base.table t1
WHERE EXISTS (SELECT 1 FROM other.table t2
              WHERE t1.name LIKE CONCAT('%', t2.name, '%'));

Then, any record in the base.table would only match if it contains some substring name from the other table.

Upvotes: 2

Related Questions