I have a campaign UTM table on Google Data Studio that I would like to re-write using REGEXP_EXTRACT so that the table only shows certain information.

This is the table that I would like to re-write data from:

I would like to only present data with the various campaign name codes if they appear in the format: HXXX, PXXX, MXXX, YXXX, VXXX or CXXX.

Based on the above table, I would only like to have these values show:

I've tried a few variants of the regex but the data doesn't pull correctly, or it shows a portion of the data.

Some examples:

Showing Null

(H|P|M|Y|V|C)[A-Z]{3}

^*(H|P|M|Y|V|C)[A-Z]{3}

(H|P|M|Y|V|C){3}

Shows the first letter of the data

(H|P|M|Y|V|C)

Using the Regex testers online shows that it works but it somehow doesn't filter on Google Data Studio.

What am I doing wrong?

CodePudding user response：

You can use

-([HPMYVC][A-Z]{3})$

See the regex demo. Note that only the part wrapped with a pair of unescaped parentheses (a capturing group) will be output.

Details:

• - - a hyphen
• ([HPMYVC][A-Z]{3}) - Group 1: [HPMYVC] matches H, P, M, Y, V or C and then [A-Z]{3} matches three uppercase ASCII letters
• $ - end of string. `

CodePudding user response：

We can try using the regex pattern [^-] $:

REGEXP_EXTRACT(Campaign, "[^-] $")