Reputation: 159
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.
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
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