CODEWITHSUNDEEP
regexlooker-studiore2
RoboPenguin
RoboPenguin

Reputation: 5

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!

Upvotes: 0

Views: 1119

Answers (1)

Nick
Nick

Reputation: 147166

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.

Upvotes: 1

Related Questions