CODEWITHSUNDEEP
sqlgoogle-bigquery
Lucas Dresl
Lucas Dresl

Reputation: 1170

scan if value of one table are in other ones without joining

I was wondering if there is a way in bigquery to scan if value of one table are in other one without doing traditional joinings since is complicated for my use case.

For example i have table phones

         phone
+4915739064510
+4915739062389
+4983149315893

And other table that is service_provider with info like this

service_code service_name
      +49157     Vodafone
     +498314        Telco

And my expected output would create a new column in phones where shows the service_name of the phone, notice that service_code length can vary, this is just some example but in reality service_code length can go from 4 to 10 making hard to join by a specific length value from phone

Expected output

         phone    service_name 
+4915739064510        Vodafone
+4915739062389        Vodafone
+4983149315893           Telco

Wanted to know if there is a way to accomplish such a thing ?

Upvotes: 0

Views: 47

Answers (2)

Mikhail Berlyant
Mikhail Berlyant

Reputation: 172993

Or just simplified/refactored version of already given answer

select phone, service_name
from phones
join service_provider
on starts_with(phone, service_code)

with output

enter image description here

Upvotes: 0

Daniel Zagales
Daniel Zagales

Reputation: 3034

Can you do something like this utilizing the STARTS_WITH function

select 
  phone,
  service_name
from phone_numbers, service 
where 
  starts_with(phone, service_code) = TRUE

This should accommodate varying lengths of input. If there is a scenario where multiple criteria match you can then apply additional logic to rank and filter down to one.

More information on the STARTS_WITH function can be found here: https://cloud.google.com/bigquery/docs/reference/standard-sql/string_functions#starts_with

Upvotes: 1

Related Questions