I have a column audit_changes in Bigquery SQL. So how can I extract the number 'shipment_id' = '28149' in another column? By the way, the free text has another number (ops_shipment_id) so it can make a mistake when extracting the 'shipment_id'.
---\nshipment_id: 28149\ninvoice_number: \nremarks: \nother_type: \nsubmitter_id: \ntax_percent: \ninput_invoice_number: \noutput_invoice_number: \naccountant_notes: \npayment_status: false\nno_tax_on_customer: \nvat_included: false\ntolls: \nfuel_prices: \ndriver_type: \nis_locked: false\nrequest_payment_time: \nreject_payment_reason: \nop_ic_id: \nreceived_shipment_photo: false\naccountant_id: \nupdate_payment_status_time: \nprice_locked: false\nassign_ac_id: \nops_shipment_id: 16735\ncancellation_fee: \nactual_pickup_addr: \nactual_dropoff_addr: \n
You can use regexp_extract():
select regexp_extract(str, '\nshipment_id: ([0-9]+)') as shipment_id