sqlgoogle-bigquerybq

Biq Query: Regexp_Contains (like in !:))

I need return true, when data in one column contain some word from another table column and use their in Case When. How I can fix it (But I can`t use join or cross join, because I need all data from left table)?

SQL

Need result

Solution

  • I need return true, when data in one column contain some word from another table column

    I find that this often works simplest to summarize the second table as a regular expression and use that:

    select m.*,
       (case when regexp_contains(m.name, d1.pattern) then 'organic'
             when regexp_contains(m.name, d2.pattern) then 'social'
        end) as source
from main_table m cross join
     (select string_agg(name, '|') as pattern
      from dictionary_1
     ) d1 cross join
     (select string_agg(name, '|') as pattern
      from dictionary_2
     ) d2;