CODEWITHSUNDEEP
postgresqllooker-studio
dan_0
dan_0

Reputation: 609

Why is REGEXP_MATCH not working like expected in Google Data Studio?

I try to generate better reports in Google Data Studio. So I started using custom fields with regular expressions, which do not work like expected.

Given for example a custom field city with the value "I love Berlin" in it i created this statement:

CASE
  WHEN REGEXP_MATCH(city,".*Berlin.*") THEN "Berlin"         
  ELSE "Other"
END

My expected result would be a match with a returned "Berlin", but I get "Other" instead.

I tried a few different things without positive results.

Upvotes: 0

Views: 1103

Answers (2)

Nimantha
Nimantha

Reputation: 6471

The CASE statement provided in the question works as expected (edit: for future reference, this was a bug, that was specific to the PostgreSQL connector, and that since been resolved; see note below).

You could have a look at adding a (capturing group) as well as adding a case insensitive flag (?i) to see if that resolves the issue:

CASE
  WHEN REGEXP_MATCH(city, ".*(?i)(Berlin).*") THEN "Berlin"
  ELSE "Other"
END

Note: The 25 Mar 2021 update explicitly states that the issue was resolved for the PostgreSQL connector:

Improved text functions in PostgreSQL
We've fixed a bug that prevented the CONTAINS_TEXT, STARTS_WITH, ENDS_WITH, and REGEXP_MATCH functions from working correctly with the PostgreSQL connector.

Editable Google Data Studio Report (Embedded Google Sheets Data Source) and a GIF to elaborate:

Upvotes: 2

dan_0
dan_0

Reputation: 609

It seems like Regexp functions can't be used with the Postgres live connector (maybe MySQL as well). I solved my problem using data extraction for the fields I needed to do regex stuff on and included the created diagrams to my report which is connected to the live connector as well.

Upvotes: 0

Related Questions