How to get value string with regexp in bigquery

Fadli Fatkhurrizki 0
Viewed 25

Hi i have string in BigQuery column like this

cancellation_amount: 602000
after_cancellation_transaction_amount: 144500
refund_time: '2022-07-31T06:05:55.215203Z'
cancellation_amount: 144500
after_cancellation_transaction_amount: 0
refund_time: '2022-08-01T01:22:45.94919Z'

i already using this logic to get cancellation_amount

regexp_extract(file,r'.*cancellation_amount:\s*([^\n\r]*)')

but the output only amount 602000, i need the output 602000 and 144500 become different column

Appreciate for helping

google-bigquery sql regex
1 Answers

If your lines in the input (which will eventually become columns) are fixed you can use multiple regexp_extracts to get all the values.

SELECT
    regexp_extract(file,r'cancellation_amount:\s*([^\n\r]*)') as cancellation_amount
    regexp_extract(file,r'. after_cancellation_transaction_amount:\s*([^\n\r]*)') as after_cancellation_transaction_amount
FROM table_name

One issue I found with your regex expression is that .*cancellation_amount won't match after_cancellation_transaction_amount.

There is also a function called regexp_extract_all which returns all the matches as an array which you can later explode into columns, but if you have finite values separating them out in different columns would be a easier.

kaysush0
Related