convert reverse domain name notation in bigquery

user_1166285 0
Viewed 70

In BigQuery, how can I convert Reverse Domain Name Rotation (com.example.subdomain) to un-reversed notation(subdomain.example.com)?

I see I can SPLIT(field, "."), but the REVERSE function accepts strings, not arrays?

google-bigquery sql
1 Answers

I have broken the the process out into multiple steps for clarity, but you could wrap all these in a 1-liner.

with 
    data as (select 'com.example.subdomain' as field),
    step1 as (select split(field,'.') as field_array from data),
    step2 as (select array_reverse(field_array) as reversed_array from step1),
    step3 as (select array_to_string(reversed_array,'.') as reversed_field from step2)
select * from step3

1-liner

with 
    data as (select 'com.example.subdomain' as field)
select array_to_string( array_reverse( split(field,'.') ),'.') as reversed_field
from data
user_116438580
Related