'Regex Positive Lookbehind alternative for BigQuery

I have a regexp that extract value from parametr in column with few extraparameters separated by "|". Parameters in field are out of sequence.

My regex: ^(?:_AB=)[^\\|]*

E.g.:

extraparams regexp_results
_A=0|_AB=0|_ABC=132|_AC=0|_ACD=TEST|_ADU=9|TEST_P=1 0
_A=0|AG=INFO|_ABC=132|_ACD=EXP|_AD=9 NULL

This solution worked until i have to using it in BigQuery. Do you have any idea to rewrite this pattern with not using Positive Lookbehind?

sqlregexgoogle-cloud-platformgoogle-bigquery


Solution 1:[1]

Will this work?

SELECT extraparams, REGEXP_REPLACE(REGEXP_EXTRACT(extraparams, r'_AB=(.+?\|){1}'), '\\|','') AS regexp_results FROM ( SELECT '_A=0|_AB=0|_ABC=132|_AC=0|_ACD=TEST|_ADU=9|TEST_P=1' AS extraparams UNION ALL SELECT '_A=0|AG=INFO|_ABC=132|_ACD=EXP|_AD=9' AS extraparams )

Solution 2:[2]

Consider also below approach

select extraparams, 
  ( select split(kv, '=')[safe_offset(1)]
    from unnest(split(extraparams, '|')) kv
    where split(kv, '=')[offset(0)] = '_AB'
  ) as regexp_results
from your_table

if applied to sample data in your question - output is

enter image description here

Sources

This article follows the attribution requirements of Stack Overflow and is licensed under CC BY-SA 3.0.

Source: Stack Overflow

Solution Source
Solution 1 Royzipuff
Solution 2 Mikhail Berlyant

Related Questions