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

Question

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!

Answer 1

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)')