AdWords is now Google Ads. Our new name reflects the full range of advertising options we offer across Search, Display, YouTube, and more. Learn more

Data Studio
4.6K members online now
4.6K members online now
Ask questions about Data Studio
Guide Me
star_border
Reply

REGEXP_REPLACE: Replace entire expression

Visitor ✭ ✭ ✭
# 1
Visitor ✭ ✭ ✭

Hi, I pulled geographic data from Facebook and values for city come in 'text' as follows 'Montreal, QC, Canada'.

 

I'm trying to break this down to also be able to report on 'Region' (ie: 'QC')

 

I thought I could use something like:

 

CASE WHEN REGEXP_MATCH(City, 'QC') THEN REGEXP_REPLACE (City, City, 'QC') ELSE 'Other' END.

 

  1. What would the appropriate syntax be for the entire expression (City) I would like to replace? 
  2. What would the appropriate syntax be for broad match (expression contains but is not limited to 'QC')

Is this the best approach for what I'm trying to do? (I've already been able to write an expression to get rid of anything after 'QC'. For example, Would a SUBSTR starting from the end of the expression work? How could it be written?

I've never used regular expressions before. If you also had resources on how to get started well, I'd be happy to benefit from them.

Thanks in advance for your help! 

REGEXP_REPLACE: Replace entire expression

Explorer ✭ ✭ ✭
# 2
Explorer ✭ ✭ ✭

Hi @Mathieu T,

 

It looks like your on the right track! Have you looked at REGEX_EXTRACT? You could use this to pull out the string between the two commas, I believe this would achieve what you are looking for?

 

Regex extract docs here:

https://support.google.com/360suite/datastudio/answer/7050487?authuser=0

 

Hope that helps,

Ben

REGEXP_REPLACE: Replace entire expression

Visitor ✭ ✭ ✭
# 3
Visitor ✭ ✭ ✭

Hi Ben,

 

I think REGEXP_EXTRACT would most definitely be the most efficient approach to doing this. Do you know what the regex syntax would be for fetching what's between the commas?

REGEXP_REPLACE: Replace entire expression

Explorer ✭ ✭ ✭
# 4
Explorer ✭ ✭ ✭

Hi @Mathieu T

 

This should do the trick:

(?<=,)[^,\n]+(?=,)

 

Hope that helps,

Ben