I'm having a hard time understanding regular expression.

I have a table mydataset.city

id | city
--- ----------------------------------------------    
1  | LOS ANGELES     NEW YORK     INDIANAPOLIS

Between each cities contain 5 spaces as delimiter.

And I want it to split them into rows like this:

id | city
--- ------------
1  | LOS ANGELES
1  | NEW YORK
1  | INDIANAPOLIS

How can I write this in bigquery?

CodePudding user response：

Use below

select id, city
from your_table, unnest(split(city, '     ')) city    

if applied to sample data in your question - output is