CODEWITHSUNDEEP
sqlgoogle-bigquery
F.D
F.D

Reputation: 837

Opposite of SUBSTR for big query

I have two tables in bigquery that can be matched on a ID. Unfortunately one of the ids has a prefix (3 digits that is not consistent) For example, one ID is "12345" (Table two / id) and the second ID is "T1_12345" (Table one / Link_id)

When selecting from the first table I can just use SUBSTR to remove the prefix before working in the second table. However, if I want to first select in the second table with the shorter prefix and than in the first table I can't find a way to do that.

The code below is an example of what i'm working with.

I'm looking for something similar to the RIGHT or SUBSTR functions, but in reverse basically. 

SELECT body from [table] where link_id in 
    (SELECT
      id
    FROM
      [table_two]
    WHERE
      author == "Username")

This code isn't correct, but might give a clearer picture of what i'm trying to do. 

SELECT body from [table] where "12345" in 
        (SELECT
          "T1_12345"
        FROM
          [table_two]
        WHERE
          author == "Username")

Edit: For example, if I had these two tables... Table 1 

| First_name|  Link ID  |
|-----------|-----------|
| James     |T1_12345   |
| John      |T2_12346   |

Table 2

| Surname|     ID    |
|-----------|--------|
| Tobbin    |12345   |
| Peterson  |12346   |

And I ran this query... 

   SELECT first_name from [table 1] where Link_ID in 
        (SELECT
          ID
        FROM
          [table_two]
        WHERE
          Surname == "Peterson")

The output I want is: John Peterson

Upvotes: 0

Views: 1067

Answers (2)

Mikhail Berlyant
Mikhail Berlyant

Reputation: 173171

Below is for BigQuery Standard SQL 

#standardSQL
SELECT first_name 
FROM `project.dataset.table_one` 
WHERE SUBSTR(Link_ID, 4) IN (
  SELECT ID
  FROM `project.dataset.table_two`
  WHERE Surname = 'Peterson'
)

with result: 

Row first_name   
1   John

-- 

#standardSQL
SELECT CONCAT(first_name, ' ', Surname) full_name
FROM `project.dataset.table_one` 
LEFT JOIN `project.dataset.table_two` 
ON SUBSTR(Link_ID, 4) = ID
WHERE Surname = 'Peterson'

with result: 

Row full_name    
1   John Peterson

Below is for BigQuery Legacy SQL 

#legacySQL
SELECT first_name 
FROM (
  SELECT First_name, SUBSTR(Link_ID, 4) short_ID 
  FROM [project:dataset.table_one]  
  )
WHERE short_ID IN (
  SELECT ID
  FROM [project:dataset.table_two]
  WHERE Surname = 'Peterson'
)

-- 

#legacySQL
SELECT CONCAT(first_name, ' ', Surname) full_name
FROM (
  SELECT First_name, SUBSTR(Link_ID, 4) short_ID 
  FROM [project:dataset.table_one]) t1
LEFT JOIN [project:dataset.table_two] t2
ON short_ID = ID
WHERE Surname = 'Peterson'

Upvotes: 2

Gordon Linoff
Gordon Linoff

Reputation: 1270873

If you want to use in, can't you just use this?

SELECT body 
FROM [table] 
WHERE link_id IN (SELECT SUBSTR(id, 4)
                  FROM [table_two]
                  WHERE author = 'Username'
                 );

Upvotes: 0

Related Questions