CODEWITHSUNDEEP
sqlgoogle-bigquery
R.Q.S
R.Q.S

Reputation: 33

Google BigQuery - To match a specific word in a column with a text in another column in different Tables

I’m trying to match different information from different tables in SQL.

I have 2 tables. Table1 with 1 column words that include words like love, passion, job... And Table2 with 1 column text that includes a fragment of a News.

I am using Big Query in Google Cloud Platform and I need a Query to match which words are in the text

I have tried this query, but it doesn't work

SELECT b.word
    ,a.text
FROM Table1 b
INNER JOIN Table2 a ON ' '+ b.word + ' ' LIKE '% ' + a.text + ' %';

I have this Information [Information in the tables]

And I want to get this [Result expected]

Thanks for your help!

Upvotes: 3

Views: 6024

Answers (2)

Mikhail Berlyant
Mikhail Berlyant

Reputation: 172984

Below example is for BigQuery Standard SQL

As you've missed provide readable input/output - I just used text of your post to make up dummy data

Hope below example shows you right way to go with 

#standardSQL
WITH table1 AS (
  SELECT 'love' AS word UNION ALL
  SELECT 'passion' UNION ALL
  SELECT 'job' UNION ALL
  SELECT 'cloud'
), table2 AS (
  SELECT '''I’m trying to match different information from different tables in SQL.''' text UNION ALL
  SELECT '''I have 2 tables. Table1 with 1 column “words” that iclude words like love, passion, job... And Table2 with 1 column “text” that include a fragment of a News.''' UNION ALL
  SELECT '''I am using Big Query in Google Cloud Platform and I need a Query to match which “words” are in the “text”''' UNION ALL
  SELECT '''I have tried this query, but it doesn't work''' UNION ALL
  SELECT '''SELECT b.word ,a.text FROM Table1 b INNER JOIN Table2 a ON ' '+ b.word + ' ' LIKE '% ' + a.text + ' %';''' UNION ALL
  SELECT '''I have this Information [Information in the tables]''' UNION ALL
  SELECT '''And I want to get this [Result expected]''' UNION ALL
  SELECT '''Thanks for your help!''' 
)
SELECT word, text
FROM table1 b
JOIN table2 a ON REGEXP_CONTAINS(text, CONCAT(r'(?i)\b', word, r'\b'))
-- ORDER BY text, word

see more about regular expression syntax

Upvotes: 4

Daria
Daria

Reputation: 606

#standardSQL
WITH
  Table1 AS (
  SELECT
    "a" AS word
  UNION ALL (
    SELECT
      "b" AS word)),
  Table2 AS (
  SELECT
    "abc" AS text
  UNION ALL (
    SELECT
      "acn" AS text))
SELECT
  a.word,
  b.text
FROM
  Table1 a
JOIN
  Table2 b
ON
  b.text LIKE CONCAT('%',a.word,'%')

Upvotes: 0

Related Questions