Split Comma separated Strings into Rows using Bigquery

Question

I'm trying to split a column that contains Strings separated by commas into rows

Input -

Expected Output -

i tried using REGEXP_EXTRACT_ALL but wasn't able to get the above output

Answer 1

Below is for BigQuery Standard SQL

#standardSQL
SELECT * EXCEPT(c) REPLACE(c AS country)
FROM `project.dataset.table`,
UNNEST(SPLIT(country)) c   

If to apply to sample data from your question as in below example

#standardSQL
WITH `project.dataset.table` AS (
  SELECT 'asia' region, 'india,china,japan' country, 100 revenue, 0.3 weight UNION ALL
  SELECT 'europe', 'uk,france,germany,italy', 75, 0.25
)
SELECT * EXCEPT(c) REPLACE(c AS country)
FROM `project.dataset.table`,
UNNEST(SPLIT(country)) c   

result is

Row region  country revenue weight   
1   asia    india   100     0.3  
2   asia    china   100     0.3  
3   asia    japan   100     0.3  
4   europe  uk      75      0.25     
5   europe  france  75      0.25     
6   europe  germany 75      0.25     
7   europe  italy   75      0.25     

Answer 2

WITH alarm_table AS (

   SELECT 'X' AS model, '1,34,60,1000' AS alarm
   union all  
   SELECT 'Y' AS model, '14,32,44,111' AS alarm
   union all  
   SELECT 'Z' AS model, '15,43,55,222' AS alarm
)

SELECT model, TRIM(alarm_time) AS alarm1 FROM alarm_table, UNNEST(SPLIT(alarm, ',')) alarm_time