CODEWITHSUNDEEP
regexlooker-studio
user3191724
user3191724

Reputation: 159

regex extract in google data studio

I am trying to extract values (in this case max and min price ) from page google analytics dimension but having a problem since the position for the max/min price can vary depending on the search that the user took.

I tried : REGEXP_EXTRACT(Page,'.*minPrice.=(.*)')

It works fine when the value is at end of the string but catches the rest of the string when the value is at the beginning or in the middle.

output

Here is an example of page values:

/search/detail/119441465?s[propertyTypes][0]=house&spropertyTypes=condo&s[minPrice]=999999&s[locations][0][city]=Ocean City&s[locations][0][state]=NJ

/search?s[propertyTypes][0]=house&spropertyTypes=condo&s[minPrice]=999000&s[orderBy][0]=r&s[locations][0][city]=Barnegat Light&s[locations][0][state]=NJ

/search/detail/118588431?s[propertyTypes][0]=house&spropertyTypes=condo&s[minPrice]=999000&s[locations][0][city]=Barnegat Light&s[locations][0][state]=NJ

/search/detail/118588431?s[city]=Barnegat Light&s[state]=NJ&s[propertyTypes][0]=house&spropertyTypes=condo&s[minPrice]=999000

/search/detail/119438690?s[orderBy]=sourceCreationDate,desc&s[locations][0][city]=Venice&s[locations][0][state]=FL&s[maxPrice]=525000&s[minPrice]=99900

/search?s[orderBy]=sourceCreationDate,desc&s[locations][0][city]=Venice&s[locations][0][state]=FL&s[maxPrice]=525000&s[minPrice]=99900

/search/detail/119337213?s[orderBy]=sourceCreationDate,desc&s[locations][0][state]=NJ&s[locations][0][county]=Monmouth County&s[maxPrice]=1488000&s[minPrice]=992000

/search/detail/119415983?s[orderBy]=sourceCreationDate,desc&s[locations][0][state]=NJ&s[locations][0][county]=Monmouth County&s[maxPrice]=1488000&s[minPrice]=992000

/search/detail/119046654?s[orderBy]=sourceCreationDate,desc&s[locations][0][state]=NJ&s[locations][0][county]=Gloucester County&s[maxPrice]=148800&s[minPrice]=99200

/search?s[orderBy]=sourceCreationDate,desc&s[locations][0][state]=NJ&s[locations][0][county]=Gloucester County&s[maxPrice]=148800&s[minPrice]=99200

/search/detail/119330920?s[orderBy]=sourceCreationDate,desc&s[locations][0][state]=NJ&s[locations][0][county]=Gloucester County&s[maxPrice]=148800&s[minPrice]=99200

Upvotes: 1

Views: 707

Answers (1)

Wiktor Stribiżew
Wiktor Stribiżew

Reputation: 627607

You can use

REGEXP_EXTRACT(Page,'[[]minPrice[]]=([0-9]+)')
REGEXP_EXTRACT(Page,r'\[minPrice]=(\d+)')

See the regex demo. Details:

  • [[]minPrice[]]= / \[minPrice]= - [minPrice]= string
  • ([0-9]+) / \d+ - Group 1: one or more digits.

The .* is not necessary as the REGEXP_EXTRACT does not anchor the matches at the start of string.

Upvotes: 1

Related Questions