Regular Expression bigquery

Question

How do I extract from an email string only the following part:

john.smith@mytest.ux.ca
al.pacino@mytext.ca

Desired result:

ux.ca
ca

Basically, everything that comes later than @ AND the first 'dot'

Answer 1

everything that comes later than @ AND the first 'dot'

You can use REGEXP_EXTRACT(email, r'@[^.]+.(.*)')

for example

#standardSQL
WITH `project.dataset.table` AS (
  SELECT 'john.smith@mytest.ux.ca' email UNION ALL
  SELECT 'al.pacino@mytext.ca'
)
SELECT 
  email, 
  REGEXP_EXTRACT(email, r'@[^.]+.(.*)')
FROM `project.dataset.table`

with result

Row email                   f0_  
1   john.smith@mytest.ux.ca ux.ca    
2   al.pacino@mytext.ca     ca