我在Bigquery SQL中有一个列audit_changes。那么，如何提取另一列中的数字'sipment_id'='28149'？顺便说一句，自由文本有另一个数字(ops_shipment_id(，因此在提取"shipment_id"时可能会出错。
---nshipment_id: 28149ninvoice_number: nremarks: nother_type: nsubmitter_id: ntax_percent: ninput_invoice_number: noutput_invoice_number: naccountant_notes: npayment_status: falsenno_tax_on_customer: nvat_included: falsentolls: nfuel_prices: ndriver_type: nis_locked: falsenrequest_payment_time: nreject_payment_reason: nop_ic_id: nreceived_shipment_photo: falsenaccountant_id: nupdate_payment_status_time: nprice_locked: falsenassign_ac_id: nops_shipment_id: 16735ncancellation_fee: nactual_pickup_addr: nactual_dropoff_addr: n
您可以使用regexp_extract():
select regexp_extract(str, 'nshipment_id: ([0-9]+)') as shipment_id