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?

Answer 1

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