Finding charaters to right of second period - Bigquery

user_19506731 0
Viewed 27

I have a BQ table that looks like this

| website           | 
| --------          |
| xyz.com           |
| abc.xyz.com       |
| 123.abc.xyz.com   |
| 098.com           |

I want to clean up this table so that I only get the domain. In my ideal world I want to execute the following steps.

  1. For each row - Count number of '.'
  2. If there are more than 1 '.', THEN extract everything to the right of the second '.' from the right. So 'abc.xyz.com' gets extracted as 'xyz.com'
  3. If there is just 1 '.', THEN do nothing and give me the input as output. So 'xyz.com' gets outputed as 'xyz.com'
google-bigquery
2 Answers

A simplier approach to extract only the domain using regex:

with sample as (
  select 'xyz.com' as website
  union all
  select 'abc.xyz.com' as website
  union all
  select '123.abc.xyz.com' as website
  union all
  select '098.com' as website
)
select 
regexp_extract(website, r"[\w]+\.[\w]+$") as website
from sample;

Output:

xyz.com
xyz.com
xyz.com
098.com

Explanation:

  • [\w]+ will match any sequence of one or more letters
  • \. will match a single dot
  • [\w]+$ will match any sequence of one or more letters only on the end of the string
user_30105480

Use below approach

select website, net.reg_domain(website) as result
from your_table

if applied to sample data in your question - output is

enter image description here

user_52219440
Related