Why is only the first case statement applied in a Google Data Studio? [duplicate]

Question

I combined these two REGEX rules in a single CASE statement in Google Data Studio, but only the first rule is applied.

CASE
    WHEN REGEXP_MATCH(Seite, "^/amp/.*") THEN REGEXP_REPLACE(Seite, "^/amp/", "")
    WHEN REGEXP_MATCH(Seite, ".*-[0-9]+$") THEN REGEXP_REPLACE(Seite, "-[0-9]+$", "")
END

If I swap the order of the rules, again only the first rule is applied; not the second one. Why is the second rule not applied? How can I make it apply both rules?

Answer 1

This is the expected behavior. CASE statement only runs the first expression that evaluates to true, not all expressions that evaluates to true.

From Data Studio documentation:

CASE evaluates each successive WHEN clause and returns the first result where the condition is true. Any remaining WHEN clauses and the ELSE result are not evaluated. If all WHEN conditions are false or NULL, CASE returns the ELSE result, or if no ELSE clause is present, returns NULL.

If you want to run multiple regex over a single string you need to create a universal regex that combines all regex or nest these rules, assuring that one rule will apply after the previous one.

---

For your specific case, I don't see a reason to run REGEXP_MATCH then REGEXP_REPLACE in a CASE statement. Instead, just run REGEXP_REPLACE and you're done (the original string will not change if the regex does not match).

Example of running multiple regex replaces in one instruction:

REGEXP_REPLACE(Seite, "(^/amp/|-[0-9]+$)", "")

Notice the use of | (pipe) separator to do an or operation. In practice, it means: "replace all parts of the string that matches ^/amp/ or -[0-9]+$ with '' (empty string)".