How to extract words from a string that end with substrings listed in an array? BigQuery-CodePudding

I have a table of rows with cells containing multiple strings. Like this:

 K1111=V1111;K1=V1;kv13_key4=--xxxxxsomething;id5=true;impid=23123123;location=domain_co_uk

I need to extract a substring that begins with kv13_key4= and ends with anything after but the lengths all vary and the substrings are all separated by a semicolon ; . I tried

REGEXP_EXTRACT(customtargeting,'%in2w_key4%;') As contains_key_Value

but didn't work. I need something like this:

| Original Cell                                                                                            | Extracted                      |
| key88=1811111;id89=9990string;K1=V1;23234234234tttttttt13_key4=--x;id5=true;impid=23123;url=domain_co_uk | kv13_key4=--x                  |
| K1111=V1111;K1=V1;kv13_key4=--xsomething;id5=true;impid=23123123;location=domain_co_uk                   | kv13_key4=--xsomething         |
| ;id5=true;T6791=V1111;K1=V1;kv13_key4=--xxxxxsomething123;impid=23123                                    | kv13_key4=--xxxxxsomething123  |

CodePudding user response：

Does this regex work:

(?<=kv13_key4=)[^;] (?=;)

It captures everything between 'kv13_key4=' and the nearest ';'

CodePudding user response：

Consider below

select *, regexp_extract(customtargeting, r'kv13_key4=[^;] ') as Extracted
from your_table

if applied to sample data in your question - output is