17

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?

Braiam
  • 1
  • 11
  • 47
  • 78
user3392296
  • 644
  • 4
  • 16
  • 16
    This question is being discussed on [Meta](https://meta.stackoverflow.com/questions/408223). – cigien Jun 08 '21 at 02:40
  • 6
    I have edited your question to better show what I think you want to ask. Please have a look over it. Maybe add context of what you actually wanted to achieve! Why did you choose the CASE statement? – NoDataDumpNoContribution Jun 08 '21 at 08:11
  • 11
    Also this question could show more research. Have you searched for the problem? What did you get? – NoDataDumpNoContribution Jun 08 '21 at 08:12
  • 2
    Why using [`CASE ... WHEN ... THEN`](https://support.google.com/datastudio/answer/7020724) when you simply want [`IF`](https://support.google.com/datastudio/answer/10468770)? – AmigoJack Jun 08 '21 at 10:06
  • Does this answer your question? [IF statement equivalent in Google Data studio](https://stackoverflow.com/a/66594195) – user692942 Jun 08 '21 at 14:35
  • 4
    @DalijaPrasnikar: That's a bit of a stretch. It's not a direct duplicate (the two questions are not the same), and the answer to this question is buried deeply in the original. – Robert Harvey Jun 09 '21 at 22:12
  • 4
    @RobertHarvey I wouldn't say it is deeply buried. Accepted answer fits on the page and the note that explains and answers what is happening here is in bold. – Dalija Prasnikar Jun 09 '21 at 22:15
  • 1
    It looks like the OP is confused by the lack of case fallthrough, @philipxy, but doesn't know the correct terminology to ask why it doesn't happen. Or as if they expected `CASE` to apply all applicable cases rather than the first applicable. – Justin Time - Reinstate Monica Jun 10 '21 at 03:23
  • 4
    @JustinTime-ReinstateMonica I don't get your point. One can guess at what the question might be if they wrote a clear question, but they didn't. Someone edited it guessing at what they meant, but it's still poorly expressed. The problem is bad writing, not terminology. (I also commented that the original wasn't clear, so close it, with 5 upvotes, but it's deleted.) PS First you say, it looks like; but then you say, or. So you don't mean it looks like, you mean it doesn't look like, it looks like maybe. So apparently you agree the question isn't clear. (And you aren't writing clearly either.) – philipxy Jun 10 '21 at 05:07
  • 1
    That's fair, @philipxy. I was going by the edited version, myself, not the original. The confusion over only the first statement being applied, and apparent desire for both to be applied, suggested to me one of two possibilities: Either the OP thought that a `CASE` statement applies all `WHEN` clauses that apply rather than just the first clause that applies, or they expected [execution to jump to the first applicable clause and continue from there](https://en.wikipedia.org/wiki/Switch_statement#Fallthrough), but didn't know the word for this type of C-like behaviour. – Justin Time - Reinstate Monica Jun 10 '21 at 17:19
  • 3
    @JustinTime-ReinstateMonica I still don't know what your point is in making these statements. The asker should be clear, & they should say what they expected & why with justification referencing authoritative documentation, otherwise this should be closed, & I had a 7-upvote comment saying that & it also got deleted. And again, no special "terminology" or "word" is needed to say what they expected. They just wrote a poor post that doesn't clearly say what they mean. Moreover if they wrote one this becomes a simple duplicate of how SQL CASE works (& they didn't research it--downvote). I'm done. – philipxy Jun 11 '21 at 07:30
  • My point is that it's my interpretation of a somewhat unclear question, @philipxy, and was addressed to your deleted comment. – Justin Time - Reinstate Monica Jun 12 '21 at 03:04

1 Answers1

29

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

Diego Queiroz
  • 3,198
  • 1
  • 24
  • 36
  • 12
    @Lino Worth mentioning that `[0-9]` and `\d` are *usually* interchangeable, but [they aren't identical](https://unix.stackexchange.com/questions/414226/difference-between-0-9-digit-and-d/414230#414230). – zcoop98 Jun 08 '21 at 14:59