Search code examples
regexlooker-studiore2

Regex Extract with two different delimiters

Working in Google Data Studio and having trouble extracting a string between two different delimiters

For example if I have the following line item:

Company_Clothes_Shirt:Red_Online_US

I would like to extract just Red

I’ve tried REGEXP_EXTRACT(Dimension,'^(?:[^\\_]*\\_){2}([^\\:]*\\:){1}') but it just gives me Shirt:

Tried several other iterations but have only been able to extract the first part (Shirt), rather than the second (Red).

Would appreciate any help on this!

Solution

  • You don't need to extract based on the whole string, you can just extract the value between the two delimiters:

    SELECT REGEXP_EXTRACT(Dimension,':([^_]+)_')

    For an input value of Company_Clothes_Shirt:Red_Online_US, this will give Red.

    Note that neither _ or : are special characters for regex, so they don't need to be escaped.