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

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  |
regexgoogle-bigquery


Solution 1:[1]

Does this regex work:

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

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

Your REGEX_EXTRACT would look like:

REGEXP_EXTRACT(customtargeting,r'(?<=kv13_key4=)[^;]+(?=;)')

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

Related Questions