ilike expression support in bigquery

BigQuery throws an error when 'ilike' expression is used. What is the alternative for doing case insensitive like query?

Below is the relevant part of the query.

SELECT id FROM `performance_last30days` WHERE device ilike 'DESKTOP'
Syntax error: Expected ")" but got "ilike" 

1 Answer

SELECT id
FROM `performance_last30days`
WHERE UPPER(device) LIKE '%DESKTOP%'

or

SELECT id
FROM `performance_last30days`
WHERE REGEXP_CONTAINS(device, r'(?i)DESKTOP')