CODEWITHSUNDEEP
sqlgoogle-bigquerysql-likesql-in
Dys_Lexi_A
Dys_Lexi_A

Reputation: 343

BigQuery Query working with multiple "likes" but not working with "in"

I would like to isolate some emails with specific titles. I can use multiple "like"s connected with an ORs in the where clause. This gives me a number of results. However, if I try to do a ____ in ('____', '____', etc), the code suddenly returns nothing.

This does not work. 

select DATE_TRUNC(DATE(send_time,"America/Los_Angeles"), week(monday)) as week,
status, 
settings_title,
sum(emails_sent) as emails_sent,
sum(report_summary_opens) as report_summary_opens, 
sum(report_summary_unique_opens) as report_summary_unique_opens, 
sum(report_summary_subscriber_clicks) as report_summary_subscriber_clicks
from mailchimp.campaigns_view
where status = 'sent' 
and settings_title in ('%_LL_%', '%_IC_%', '%_AC_%', '%_CC_%', '%_PC_%')
group by 1,2,3
order by 1 desc

However, this works. 

select DATE_TRUNC(DATE(send_time,"America/Los_Angeles"), week(monday)) as week,
status, 
settings_title,
sum(emails_sent) as emails_sent,
sum(report_summary_opens) as report_summary_opens, 
sum(report_summary_unique_opens) as report_summary_unique_opens, 
sum(report_summary_subscriber_clicks) as report_summary_subscriber_clicks
from mailchimp.campaigns_view
where status = 'sent' 
and (settings_title like '%_LL_%'
or settings_title like '%_IC_%'
or settings_title like '%_AC_%'
or settings_title like '%_CC_%'
or settings_title like '%_PC_%')
group by 1,2,3
order by 1 desc

I have already tried to include a subquery in my "from" that eliminates all null settings_title. Any ideas why this is not working? Am I missing some small syntax error?

Thanks for the help!

Upvotes: 0

Views: 12263

Answers (2)

dkl
dkl

Reputation: 1

Thanks Felipe, very usefull!!!. In my case I used REGEXP_CONTAINS for matching with a multiple patterns added to a table. The select with the column "pattern_str" located in the second position is able to search and find correctly for every portion of the parttern: WITH CTE_PatternCovid as ( Select STRING_AGG(Pattern,'|') as strPattern from xxxxxxxxx.TEMP.Temp_patternsearch_covid  ) --this convert the multiple patterns into a single line: --.*MASK.FFP.|.*MASK.KN.|.*TEST.ANTIG.|.*MASK.QUI.|.*SP.*H.DRO.AL. --then use in this way: Select ProductName FROM xxxxxxxxx.TEMP.Table_ProductsName_covid  where regexp_contains (upper(ProductName),(SELECT strPattern FROM CTE_PatternCovid ))

Upvotes: 0

Felipe Hoffa
Felipe Hoffa

Reputation: 59175

The % symbol will only work with LIKE. For IN it's only equality. Try REGEXP_CONTAINS too.

As in:

SELECT REGEXP_CONTAINS("abcdefg", '(xxx|zzz|yyy|cd)')

Upvotes: 11

Related Questions