CODEWITHSUNDEEP
google-bigquerysubquery
Ujwala Nanavare
Ujwala Nanavare

Reputation: 9

How to write a BIGQuery query to get a list of some words and check if they contain in another String?

I need to get some content with the words containing from wordlist I have tried following but not getting exact results.

SELECT q.id, q.title from `questions`q JOIN `words` ON REGEXP_CONTAINS(q.title,word) limit 50

Data Words:[Mango, Tire, web, gray, Apple]

expected Questions from the database: 1. What web browser do you prefer? 2. Do you have gray hairs? 3. Which is your fav Mango or apple

Upvotes: 1

Views: 561

Answers (1)

Mikhail Berlyant
Mikhail Berlyant

Reputation: 172993

Below example for BigQuery Standard SQL 

#standardSQL
WITH `project.dataset.questions` AS (
  SELECT 1 id, "What web browser do you prefer?" title UNION ALL
  SELECT 2, "Do you have gray hairs?" UNION ALL
  SELECT 3, "Which is your fav Mango or apple?" UNION ALL
  SELECT 4, "How to write a BIGQuery query to get a list of some words and check if they contain in another String?" UNION ALL
  SELECT 5, "Is there any free way to search github that isn't severely limited and simple (aka: searching for special characters or using regex)?" UNION ALL
  SELECT 6, "Is there any way to run a portion of a large query on bigquery when the free quota doesn't allow it?" UNION ALL
  SELECT 7, "How to unpivot in BigQuery?" UNION ALL
  SELECT 8, "how get rows with latest date?" UNION ALL
  SELECT 9, "Have you heard of weblogic?"
), `project.dataset.words` AS (
  SELECT 'Mango' word UNION ALL
  SELECT 'Tire' UNION ALL
  SELECT 'web' UNION ALL
  SELECT 'gray' UNION ALL
  SELECT 'Apple' UNION ALL
  SELECT 'BigQuery'
)
SELECT DISTINCT q.id, q.title 
FROM `project.dataset.questions` q 
JOIN `project.dataset.words` 
ON REGEXP_CONTAINS(LOWER(q.title), r'\b' || LOWER(word) || r'\b') 
LIMIT 50

with output 

Row id  title    
1   1   What web browser do you prefer?  
2   2   Do you have gray hairs?  
3   3   Which is your fav Mango or apple?    
4   4   How to write a BIGQuery query to get a list of some words and check if they contain in another String?   
5   6   Is there any way to run a portion of a large query on bigquery when the free quota doesn't allow it?     
6   7   How to unpivot in BigQuery?

To run it agains your own data - just remove TCE (WITH statements) and use your real tables references

Upvotes: 1

Related Questions