dimanche 26 juillet 2020

BigQuery: split string with repetitive pattern to separate rows

I have a string with repetitive pattern like: timestampField: <SOME_INT_VALUE> url: <SOME_STRING_VALUE>. As you can see in the example, this pattern returns multiple (4 in this example) times.

SELECT my_string
FROM (SELECT 'timestampField: 1595351350 url: domainZ/aaaabbbbccccdddd timestampField: 1595351355 url: domainZ/eeeeffffgggghhhh timestampField: 1595351355 url: domainZ/iiiijjjjkkkkllll timestampField: 1595351356 url: domainZ/mmmmnnnnppppoooo' as my_string)

I want to break this big string into 4 (in this example) different rows, each row will have a string value

  1. timestampField: 1595351350 url: domainZ/aaaabbbbccccdddd
  2. timestampField: 1595351355 url: domainZ/eeeeffffgggghhhh
  3. timestampField: 1595351355 url: domainZ/iiiijjjjkkkkllll
  4. timestampField: 1595351356 url: domainZ/mmmmnnnnppppoooo

Aucun commentaire:

Enregistrer un commentaire