3

I am trying to remove square brackets around a date field in Google Data Studio so I can properly treat it as a proper date dimension.

It looks like this:

[2020-05-20 00:00:23]

and I am using the RegEx of REGEXP_REPLACE(Date, "/[\[\]']+/g", "") and I want it to look like this for the output:

2020-05-20 00:00:23

It keeps giving me error results and will not work. I can not figure out what I am doing wrong here, I've used https://www.regextester.com/ to verify that it should work

logi-kal
  • 7,107
  • 6
  • 31
  • 43
Ryan Coolwebs
  • 1,611
  • 5
  • 22
  • 44

2 Answers2

2

You need to use a plain regex pattern, not a regex literal notation (/.../g).

Note that REGEXP_REPLACE removes all occurrences found, thus, there is no need for a g flag.

Use

REGEXP_REPLACE(Date, "[][]+", "")

to remove all square brackets in Date.

Wiktor Stribiżew
  • 607,720
  • 39
  • 448
  • 563
2

Regarding Dates, it can be achieved with a single TODATE Calculated Field:

TODATE(Date, "[%Y-%m-%d %H:%M:%S]", "%Y%m%d%H%M%S")

The Date Type can then be set as required:

  • YYYYMMDD: Date
  • YYYYMMDDhh: Date Hour
  • YYYYMMDDhhmm: Date Hour Minute

Google Data Studio Report and GIF to elaborate:

Nimantha
  • 6,405
  • 6
  • 28
  • 69