0

I need to separate text in columns in BigQuery

my data follow this pattern: ID-006952_PROM_GEN_HOME-APP_RET8_M10_20201023_LIQUIDA_ATE-70-OFF

I need each column separated by "_"

Mikhail Berlyant
  • 117,385
  • 6
  • 77
  • 139
Vinicius Matsumoto
  • 11
  • 2
    Looks like you are looking to create a regex, but do not know where to get started. Please check [Reference - What does this regex mean](https://stackoverflow.com/questions/22937618) resource, it has plenty of hints. Also, refer to [Learning Regular Expressions](https://stackoverflow.com/questions/4736) post for some basic regex info. Once you get some expression ready and still have issues with the solution, please edit the question with the latest details and we'll be glad to help you fix the problem. – Wiktor Stribiżew Oct 27 '20 at 20:57

1 Answers1

0

Below is for BigQuery Standard SQL

#standardSQL
select 
  arr[safe_offset(0)] as id, 
  arr[safe_offset(1)] as col2, 
  arr[safe_offset(2)] as col3, 
  arr[safe_offset(3)] as col4, 
  arr[safe_offset(4)] as col5, 
  arr[safe_offset(5)] as col6, 
  arr[safe_offset(6)] as col7, 
  arr[safe_offset(7)] as col8, 
  arr[safe_offset(8)] as col9
from `project.dataset.table` t,
unnest([struct(split(data, '_') as arr)])

if to apply to sample data from your question - output is

enter image description here

Mikhail Berlyant
  • 117,385
  • 6
  • 77
  • 139