regexlooker-studioregexp-substrre2xregexp

Regex Extract Google Data Studio: Need to slice one field delimited with pipes into separate fields


I have a field in which values look like:

Field
pos1-123|pos2 xyx123|pos3-abc|pos4x350

I want to slice the field into four different calculated fields using REGEXP_EXTRACT that look like:

I've managed to pull Calculated Field 1 on my own by using:

> REGEXP_EXTRACT(Field, '^//|(//|[[:alnum:]]+)')

However, I'm getting stuck on iterating through the rest of the string.


Solution

  • You can use the following regular expressions:

    REGEXP_EXTRACT(Field, '^([^|]+)')
    REGEXP_EXTRACT(Field, '^[^|]+\\|([^|]+)')
    REGEXP_EXTRACT(Field, '^(?:[^|]+\\|){2}([^|]+)')
    REGEXP_EXTRACT(Field, '^(?:[^|]+\\|){3}([^|]+)')
    

    Details: