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
3.9K members online now
3.9K members online now
Ask questions about Data Studio
Guide Me
star_border
Reply

RegEx Escape Character in Data Studio

Follower ✭ ✭ ☆
# 1
Follower ✭ ✭ ☆

It seems like the backslash in Data Studio (for calculated fields like REGEXP_REPLACE()) does not escape special characters and even throws an error. Has anyone seen a workaround or experienced the same thing?

RegEx Escape Character in Data Studio

Explorer ✭ ✭ ✭
# 2
Explorer ✭ ✭ ✭

Hi @Samantha B,

 

That's interesting - I've not had to escape any characters in my reports, but it would be good to know more about this, whats the regex you are running?

 

Ben

RegEx Escape Character in Data Studio

[ Edited ]
Visitor ✭ ✭ ✭
# 3
Visitor ✭ ✭ ✭

Hi @Samantha B,

 

I had a use-case where I needed to get the name of an application which was started in Windows.

The raw data was in the following format (subset of raw data below):

 

C:\Windows\System32\mmc.exe

 

To extract only "mmc.exe", that is, remove all the " \ ", I used the following regexp_replace in my Calculated field in Data Studio:

 

REGEXP_REPLACE(text.message, r'.*[[: punct:]]', '')

 

NB: Remove the 'space' between : and p in the above.

 

Reference: https://github.com/google/re2/wiki/Syntax

 

RegEx Escape Character in Data Studio

Follower ✭ ✭ ☆
# 4
Follower ✭ ✭ ☆

That's how I got around it as well, but others don't seem to work from that ref doc or I am misunderstanding (for example, [[:graph:]] for '+' characters).

 

The backspace escape literal is there as well and listed as supported, so I'm not sure what's going on here.

RegEx Escape Character in Data Studio

Visitor ✭ ✭ ✭
# 5
Visitor ✭ ✭ ✭

I have noticed a disconnect for the "regexp_replace" syntax used between BigQuery and Data Studio.

For e.g. the following works as the escape character for a backslash in BigQuery SQL: r".*\\"

This above throws an invalid formula error in Data Studio though.

 

As a result, I have had to use multiple variants of the "regexp_replace" syntax (trial and error basically) to get it working in Data Studio.

RegEx Escape Character in Data Studio

Follower ✭ ✭ ☆
# 6
Follower ✭ ✭ ☆

That has been my experience as well. Hoping to get some insight from the Google team on the differences in DS regex and 'normal' soon!

RegEx Escape Character in Data Studio

Visitor ✭ ✭ ✭
# 7
Visitor ✭ ✭ ✭

I have a similar issue. I want to search for .gov domains but don't know how to escape the period before 'gov'. 

 

REGEXP_MATCH(EmailAddress, '.*\.gov.*'  ) gives me an error

RegEx Escape Character in Data Studio

[ Edited ]
Visitor ✭ ✭ ✭
# 8
Visitor ✭ ✭ ✭

Just read an answer on this thread: https://www.en.advertisercommunity.com/t5/Data-Studio/Creating-a-new-dimension-with-REGEXP-EXTRACT-n...

 

The solution is to double-escape the special characters (i.e. \\).

RegEx Escape Character in Data Studio

Follower ✭ ✭ ☆
# 9
Follower ✭ ✭ ☆

I got that response in another forum today as well, working so far! Thanks for updating.

RegEx Escape Character in Data Studio

Visitor ✭ ✭ ✭
# 10
Visitor ✭ ✭ ✭

It seems that no longer works (double-escaping).

Now it works by quadruple-escaping it. Yes, really:

So regex '.*\.gov.*' has to be written in Data Studio as '.*\\\\.gov.*', which then DS passes to BQ "unsecaped" as '.*\\.gov.*', which works fine.