I want to know how to execute CONNECT BY REGEXP in Google Big query

Question

I have following statement in oracle sql I want to run this in Google Big Query.

CONNECT BY REGEXP_SUBSTR(VALUE, '[^,]+', 1, LEVEL) IS NOT NULL)

How can I run above code in Big query?

Answer 1

I am guessing here - but usually this construct is used for so called string decomposition

So, in BigQuery you can use SPLIT(value) or REGEXP_EXTRACT_ALL(value, r'[^,]+') for this as in below examples

#standardSQL
WITH `project.dataset.table` AS (
  SELECT 1 id, '1,2,3,4,5,6,7' AS value UNION ALL
  SELECT 2, 'a,b,c,d'
)
SELECT id, SPLIT(value) value
FROM `project.dataset.table`   

or

#standardSQL
WITH `project.dataset.table` AS (
  SELECT 1 id, '1,2,3,4,5,6,7' AS value UNION ALL
  SELECT 2, 'a,b,c,d'
)
SELECT id, REGEXP_EXTRACT_ALL(value, r'[^,]+') value
FROM `project.dataset.table`  

both above query will return

Row id  value    
1   1   1    
        2    
        3    
        4    
        5    
        6    
        7    
2   2   a    
        b    
        c    
        d     

Here, as you can see - value in each row gets split into array of elements but still in the same row

To flatten result you can further use UNNEST() as in below examples

#standardSQL
WITH `project.dataset.table` AS (
  SELECT 1 id, '1,2,3,4,5,6,7' AS value UNION ALL
  SELECT 2, 'a,b,c,d'
)
SELECT id, value
FROM `project.dataset.table`,
UNNEST(SPLIT(value)) value  

or

#standardSQL
WITH `project.dataset.table` AS (
  SELECT 1 id, '1,2,3,4,5,6,7' AS value UNION ALL
  SELECT 2, 'a,b,c,d'
)
SELECT id, value
FROM `project.dataset.table`, 
UNNEST(REGEXP_EXTRACT_ALL(value, r'[^,]+')) value   

both return below result (with all extracted elements in separate row)

Row id  value    
1   1   1    
2   1   2    
3   1   3    
4   1   4    
5   1   5    
6   1   6    
7   1   7    
8   2   a    
9   2   b    
10  2   c    
11  2   d