I have a string that is set up as date_type_campaignname_audience_timeofday. I would like to set up 5 regexp expressions to pull out each substring.
I tried this to pull out the type and it instead pull the audience: REGEXP_EXTRACT(field_name, r"(.*?)_([^_]*)_").
>Solution :
Match ignored fragments, then capture the fragment you need:
REGEXP_EXTRACT(field_name, '^(?:[^_]+_){0}([^_]+)') // date
REGEXP_EXTRACT(field_name, '^(?:[^_]+_){1}([^_]+)') // time
REGEXP_EXTRACT(field_name, '^(?:[^_]+_){2}([^_]+)') // campaignname
REGEXP_EXTRACT(field_name, '^(?:[^_]+_){3}([^_]+)') // audience
REGEXP_EXTRACT(field_name, '^(?:[^_]+_){4}([^_]+)') // timeofday
Try it on regex101.com. (Increase/decrease the number to see what the matches look like.)
[^_]+ matches a fragment consisting of 1 or more non-underscore characters.
(?:[^_]+_){3} matches three times of such a fragment followed by an underscore (e.g. a_b_c_). ^ means "at the start".
Collectively, these regexes match the first, second, third, fourth and fifth fragments of an underscore-separated string.
Credit goes to the author of this answer.