Using the Split Function in BigQuery

Working with Strings in Google BigQuery

Photo by James Lee on Unsplash

When working with Google BigQuery and SQL you may come across text values stored in arrays. For example, as in the example below:

Text in Array Example Data — Image by Author

Here, the SPLIT() function can help you. It divides value using the delimiter argument. For STRING, the default delimiter is a comma.
For BYTES, you must specify a delimiter. Splitting with an empty delimiter generates an array of UTF-8 characters for STRING values and an array of BYTESfor BYTESvalues. Splitting an empty STRING returns an ARRAYof a single empty STRING [1].

Here an example with the data from above:

WITH fruits AS
 (SELECT “apple” as fruit
 UNION ALL
 SELECT “apple,bananas” as fruit
 UNION ALL
SELECT “apple,bananas,orange” as fruit)
SELECT SPLIT(fruit, “,”) as example FROM fruits;

and the result:

Result of the Split Function — Image by Author