29 Mar 19 · DataFreak · #bigquery ·   Bookmark   ×

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 Mar 19

Enjoy great content like this and a lot more !

Signup for a free account to write a post / comment / upvote posts. Its simple and takes less than 5 seconds