CODEWITHSUNDEEP
google-bigquerywildcard
Alex
Alex

Reputation: 191

Wildcard % operator not working on bigquery

On Bigquery, am trying to select WHERE item IN ('%xxxxxxxxxx', '%xxxxxxxxxx'). However when I pull that it doesn't return any results. However if I get rid of the wildcard % it does return results. Am I using the wrong wildcard operator here?

Thanks!

Alex

Upvotes: 0

Views: 8483

Answers (2)

Mikhail Berlyant
Mikhail Berlyant

Reputation: 172994

Below is for BigQuery Standard SQL

#standardSQL
SELECT *
FROM YourTable
WHERE REGEXP_CONTAINS(item, r'xxxxxxxxxx$|yyyyyyyy$')

You can play with it with dummy data as below 

#standardSQL
WITH data AS (
  SELECT * 
  FROM UNNEST(['a_item', 'b_item', 'a_piece', 'b_piece', 'a_part', 'b_part']) AS item
)
SELECT *
FROM data
WHERE REGEXP_CONTAINS(item, r'item$|part$')

See more about REGEXP_CONTAINS

Upvotes: 1

Elliott Brossard
Elliott Brossard

Reputation: 33705

You need to use the LIKE operator instead. For example,

#standardSQL
SELECT *
FROM YourTable
WHERE EXISTS (
  SELECT 1
  FROM UNNEST(['%pattern1', '%pattern2']) AS pattern
  WHERE item LIKE pattern
);

Upvotes: 1

Related Questions