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\nWHERE (Col1 LIKE '%/golang/%' \nOR Col1 LIKE '%/sqlinfo/%'\nOR Col1 LIKE '%/sprok/%')\n\nResult:\nXXXX/sqlinfo/XXX\n

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

WITH table AS (\n  SELECT 'XXXX/sqlinfo/XXX' Col1\n)\n\nSELECT * FROM TABLE\nWHERE REGEXP_CONTAINS(Col1, '/(golang|sqlinfo|sprok)/')\n\nReturns:\nXXXX/sqlinfo/XXX\n

DataFreak

posted on