Regex Positive Lookbehind alternative for BigQuery-CodePudding

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?

CodePudding user response：

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 )

CodePudding user response：

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