'How to split string into rows with space delimiter in Bigquery?
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?
Solution 1:[1]
Use below
select id, city
from your_table, unnest(split(city, ' ')) city
if applied to sample data in your question - output is
Sources
This article follows the attribution requirements of Stack Overflow and is licensed under CC BY-SA 3.0.
Source: Stack Overflow
| Solution | Source |
|---|---|
| Solution 1 | Mikhail Berlyant |