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;