BigQuery/SQL: Split String based on the second dot from right

update: there are situations that dot position that might not be the best solution.

I got a column of website.

website
www.abc.google.com
www.bcd.google.com
wwww.efd.google.co.za

I want to transform it into

    website
    google.com
    google.com
    google.co.za

Anyone knows how to split based on the ‘.’ position from the right?

Thanks.

Answer

regexp_substr() does exactly what you want:

select regexp_substr('www.abc.google.com', '[^.]*[.][^.]*$')