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

Question

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:

• Calculated Field 1 : pos1-123,
• Calculated Field 2 : pos2 xyx123
• Calculated Field 3 : pos3-abc
• Calculated Field 4 : pos4x350

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.

Answer 1

You can use the following regular expressions:

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

Details:

• ^ - start of string
• (?:[^|]+\\|){3} - three occurrences ({3}) of
  • [^|]+ - any one or more chars other than |
  • \| - a | char
• ([^|]+) - Capturing group 1: any one or more chars other than |.