Extract value after specific sequence of string in BigQuery

Question

I have the following example of strings in BigQuery:

string                                                                        
action_1 plan_id=266 revenue=123.93                                                    
action_2 plan_id=057 revenue=33.54 action_1 plan_id=432 revenue=127.12                 
action_4 plan_id=854 revenue=123.46 action_1 plan_id=138 revenue=98.43
action_3 plan_id=266 revenue=123.93

What I want to extract is the value of the revenue after action_1. So, the new field should be this:

string                                                                   action_1_revenue 
action_1 plan_id=266 revenue=123.93                                                123.93
action_2 plan_id=057 revenue=33.54 action_1 plan_id=432 revenue=127.12             127.12
action_4 plan_id=854 revenue=123.46 action_1 plan_id=138 revenue=98.43              98.43
action_3 plan_id=266 revenue=123.93                                                  NULL

Any ideas?

Mikhail Berlyant · Accepted Answer · 2022-03-02 18:11:11Z

2

Consider below

select *, regexp_extract(string, r'action_1 .* revenue=([\d.]+)') action_1_revenue 
from your_table

if applied to sample data in your question - output is

answered Mar 2, 2022 at 18:11

Mikhail Berlyant

174k10 gold badges173 silver badges251 bronze badges

Sign up to request clarification or add additional context in comments.

Collectives™ on Stack Overflow

Extract value after specific sequence of string in BigQuery

1 Answer 1

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

Comments

Your Answer

Sign up or log in

Post as a guest

Related