mysql - Comparing substrings in Google BigQuery -

i want query 2 table depending on condition , want generate tag can see in image yes/maybe

for displaying above result using if(places.name contains poi.name 'yes','maybe') problem : in image on line no. 4 poi_name contain value surana.agen , respective column places_type have value [w1]surana.agency , hence want tag yes instead of maybe. poi_name column can have special character here dot(.) want split columns values whichever special character present , in case want search surana or agen present in places_name.

any appreciable

at high level, i'd suggest thinking problem follows:

• step 1: split poi_name substrings want match.
• step 2: check whether of substrings contained in places_name.

for step 1, it's hard pull apart arbitrary number of substrings in sql. however, if have limit in mind (e.g., @ 3 substrings), pull them out using regexp_extract. example:

 regexp_extract(poi_name, r'([^.]*)') first,  regexp_extract(poi_name, r'[^.]*\.([^.]*)') second,  regexp_extract(poi_name, r'[^.]*\.[^.]*\.([^.]*)') third, 

fortunately, step 2 easy. once you've extracted strings, use contains test whether they're in places_name.

i'm not sure how control have on input, might consider splitting poi_name separate fields or repeated field in advance, since avoid need run regexp_extract on every query.