Splitting a string column in BigQuery

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).

Trying to rewrite Elad Ben Akoune's answer in Standart SQL, the query becomes like this;

WITH name_score AS (
SELECT Name, split(Scores,';') AS Score
FROM (
      (SELECT * FROM (SELECT 'Bob' AS Name ,'10;20;20' AS Scores)) 
      UNION ALL 
      (SELECT * FROM (SELECT 'Sue' AS Name ,'14;12;19;90' AS Scores))
      UNION ALL
      (SELECT * FROM (SELECT 'Joe' AS Name ,'30;15' AS Scores))
)) 
SELECT name, score
FROM name_score
CROSS JOIN UNNEST(name_score.score) AS score;

And this outputs;

+------+-------+
| name | score |
+------+-------+
| Bob  | 10    |
| Bob  | 20    |
| Bob  | 20    |
| Sue  | 14    |
| Sue  | 12    |
| Sue  | 19    |
| Sue  | 90    |
| Joe  | 30    |
| Joe  | 15    |
+------+-------+