bigquery join tables using regex

0
votes

I have two tables both have a column called host. I would like to join these two tables by some part of host, like below which is working:

select * from `config.tested` a
join `config.active` b
on REGEXP_EXTRACT(a.host,  r'.*req-([a-z0-9]{12})')=REGEXP_EXTRACT(b.host,  r'.*req-([a-z0-9]{12})')

is there any more efficient way?like USING(REGEXP_EXTRACT(host, r'.*req-([a-z0-9]{12})')) it does not work

2
sqlgoogle-bigquery

2 Answers

1
votes

I see no problem with your logic, but you could use left():

ON LEFT(a.host, 16) = LEFT(b.host, 16)

This includes the "reg-" as well as the final string.

If you still need to ensure that the values match the pattern you want, then add:

WHERE REGEXP_CONTAINS(a.host, r'.*req-([a-z0-9]{12})')
1
votes

is there any more efficient way?

If by "more efficient" you mean less verbose and easier to read / maintain - try below

#standardSQL
create temp function part(host string) as (
  regexp_extract(host, r'.*req-([a-z0-9]{12})')
);
select * 
from `config.tested` a 
join `config.active` b
on part(a.host) = part(b.host)

or its variation