Search code examples
google-bigquery

Splitting a string column in BigQuery

Let's say I have a table in BigQuery containing 2 columns. The first column represents a name, and the second is a delimited list of values, of arbitrary length. Example:

Name | Scores
-----+-------
Bob  |10;20;20
Sue  |14;12;19;90
Joe  |30;15

I want to transform into columns where the first is the name, and the second is a single score value, like so:

Name,Score
Bob,10
Bob,20
Bob,20
Sue,14
Sue,12
Sue,19
Sue,90
Joe,30
Joe,15

Can this be done in BigQuery alone?

Solution

  • Good news everyone! BigQuery can now SPLIT()!

    Look at "find all two word phrases that appear in more than one row in a dataset".

    There is no current way to split() a value in BigQuery to generate multiple rows from a string, but you could use a regular expression to look for the commas and find the first value. Then run a similar query to find the 2nd value, and so on. They can all be merged into only one query, using the pattern presented in the above example (UNION through commas).