How to implement "LIKE ANY" in BigQuery ?

LIKE ANY Clause is used to do pattern matching on multiple values. This Clause is not supported by Big query. For example:

WITH table AS (
  SELECT 'XXXX/sqlinfo/XXX' as col1
)
SELECT * FROM TABLE
WHERE col1 LIKE ANY ( 
  '%/golang/%'  '%/sqlinfo/%',  '%/sprok/%'
)
Syntax error: Unexpected keyword ANY at [2:6]

You can fix this error by using one of the below methods.

Method #1: Replace LIKE ANY with a series of LIKE OR

SELECT * FROM TABLE
WHERE (Col1 LIKE '%/golang/%' 
OR Col1 LIKE '%/sqlinfo/%'
OR Col1 LIKE '%/sprok/%')

Result:
XXXX/sqlinfo/XXX

Method #2: Combine LIKE ANY Values into one regular expression

WITH table AS (
  SELECT 'XXXX/sqlinfo/XXX' Col1
)

SELECT * FROM TABLE
WHERE REGEXP_CONTAINS(Col1, '/(golang|sqlinfo|sprok)/')

Returns:
XXXX/sqlinfo/XXX

DataFreak

posted on 29 Nov 21