如何使用Regex替换Google Data Studio中日期字段中的方括号?

我正在尝试在Google Data Studio中删除日期字段周围的方括号,以便将其正确地视为正确的日期维度。

看起来像这样:

[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

评论
  • wquod
    wquod 回复

    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.

    采用

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

    to remove all square brackets in Date.

  • 婚礼礼堂
    婚礼礼堂 回复

    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")
    

    然后可以根据需要设置日期类型:

    • YYYYMMDD:日期
    • YYYYMMDD:日期小时
    • YYYYMMDDhhmmss:日期小时分钟

    Google Data Studio Report and GIF to elaborate: