ilike expression support in bigquery

Question

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" 

Answer 1

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

or

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