How to use an underscore character in a LIKE filter give me all the results from a column

Question

I am trying to filter my sql query with a like condition using underscore in my where clause. However, when i am filtering i want all values with just TS_AW19 and not all values which include TS which is what my query is currently giving me. Could someone assist me with the correct syntax to use for my query below?

SELECT 
date,
creative_name,
SUM(revenue)*2 as spend
FROM `crate-media-group-client-data.DV360_ALL.GGLDV360BM_CREATIVE_*`
WHERE advertiser LIKE '%Topshop/Topman%' AND creative_name LIKE '%TS_AW19%' AND date = '2019-09-20'
GROUP BY 1,2 
ORDER by creative_name

Note: i am using big query syntax for this query

Answer 1

Underscore (and percent) has a special meaning when used with LIKE, and means a wildcard for any single character. To workaround this, from the BigQuery documentation for LIKE:

You can escape "\", "_", or "%" using two backslashes. For example, "\%". If you are using raw strings, only a single backslash is required. For example, r"\%".

You may try double-escaping the underscore, if you intend for it be literal in your LIKE expression:

SELECT 
    date,
    creative_name,
    SUM(revenue)*2 AS spend
FROM `crate-media-group-client-data.DV360_ALL.GGLDV360BM_CREATIVE_*`
WHERE
    advertiser LIKE '%Topshop/Topman%' AND
    creative_name LIKE '%TS\\_AW19%' AND
    date = '2019-09-20'
GROUP BY 1,2 
ORDER BY
    creative_name;