'Splitting the string into columns to extract values using BigQuery

How can i split the string by a specific character and extract the value of each. The idea is that i need to extract each word between the line including the start/end of the string as this information represents something. Is there a regex pattern ? or a way to split the info into columns ?

Name
A|B|C|D|E|F|G


Name           col1  col2  col3  col4 col5   col6  col7
A|B|C|D|E|F|G   A     B     C     D     E      F    G

I am using BigQuery for this and couldn't find a way to get the info of all of those. I tried the regex code which only works for the case where we have A|B|C.

I have to compare each column value and then create conditions using case when

CODE:

select
  regexp_extract(name, "\\w+\\S(x|y)") as c2, -- gives either x or y
  left(regexp_substr(name, "\\w+\\S\\w+\\S\\w+"),1) as c1, 
  right(regexp_extract(name, "\\w+\\S\\w+\\S\\w+"),1) as c3 
from Table
sqlregexsplitgoogle-bigquery


Solution 1:[1]

Consider below approach

select * from (
  select *
  from  your_table, unnest(split(name, '|')) value with offset
)
pivot(any_value(value) as col for offset in (0,1,2,3,4,5,6))

if applied to dummy data as in your question - output is

enter image description here

Solution 2:[2]

This seems like a use case for SPLIT().

select split(name,"|")[safe_offset(0)] as c1, split(name,"|")[safe_offset(1)] as c2, ..
from table

see https://cloud.google.com/bigquery/docs/reference/standard-sql/string_functions#split

Added use of safe_offset instead of offset per Array index 74 is out of bounds (overflow) google big query

Sources

This article follows the attribution requirements of Stack Overflow and is licensed under CC BY-SA 3.0.

Source: Stack Overflow

Solution Source
Solution 1 Mikhail Berlyant
Solution 2

Related Questions