Splitting the string into columns to extract values using BigQuery-CodePudding

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

CodePudding user response：

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

CodePudding user response：

This seems like a use case for SPLIT().

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

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