Google Data Studio中如何使用多条件正则表达式？

Got it, let's fix this issue. Your current CASE statement works for single-tag campaigns because it stops at the first matching condition—but when a campaign has multiple tags (like BLUCAR or GREMOT), it only returns the first match instead of capturing all relevant categories, or prioritizing the right one if that's what you need. Here are two tailored solutions:

Option 1: Return All Matching Categories (As Array or String) #

If you want to capture every applicable category for a multi-tag campaign, use a mapping CTE combined with REGEXP_CONTAINS to join and aggregate matches. This approach is clean and scalable if you add more tags later:

WITH tag_category_mapping AS (
  SELECT "BLU" AS tag, "Colour Blue" AS category UNION ALL
  SELECT "GRE" AS tag, "Colour Green" UNION ALL
  SELECT "CAR" AS tag, "Product Car" UNION ALL
  SELECT "MOT" AS tag, "Product Motorbike"
)
SELECT
  Campaign,
  -- Return as an array (ideal for further analysis)
  ARRAY_AGG(category) AS campaign_categories,
  -- Or return as a comma-separated string if you need plain text
  STRING_AGG(category, ", ") AS campaign_categories_text
FROM your_table_name
JOIN tag_category_mapping
ON REGEXP_CONTAINS(Campaign, tag)
GROUP BY Campaign

Alternatively, if you prefer a concise inline approach without a CTE:

SELECT
  Campaign,
  ARRAY(
    SELECT cat
    FROM UNNEST([
      IF(REGEXP_CONTAINS(Campaign, "BLU"), "Colour Blue", NULL),
      IF(REGEXP_CONTAINS(Campaign, "GRE"), "Colour Green", NULL),
      IF(REGEXP_CONTAINS(Campaign, "CAR"), "Product Car", NULL),
      IF(REGEXP_CONTAINS(Campaign, "MOT"), "Product Motorbike", NULL)
    ]) cat
    WHERE cat IS NOT NULL
  ) AS campaign_categories
FROM your_table_name

Option 2: Prioritize Specific Categories (Single Output) #

If you only need one category per campaign and want to prioritize certain tags (e.g., product tags over color tags), reorder your CASE conditions to check higher-priority tags first. Also, swap REGEXP_MATCH with REGEXP_CONTAINS—REGEXP_MATCH requires the entire string to match, while REGEXP_CONTAINS checks for the substring, which is almost certainly what you intended:

SELECT
  Campaign,
  CASE
    WHEN REGEXP_CONTAINS(Campaign, "CAR") THEN "Product Car"
    WHEN REGEXP_CONTAINS(Campaign, "MOT") THEN "Product Motorbike"
    WHEN REGEXP_CONTAINS(Campaign, "BLU") THEN "Colour Blue"
    WHEN REGEXP_CONTAINS(Campaign, "GRE") THEN "Colour Green"
    ELSE "Uncategorized" -- Add a default category if needed
  END AS primary_campaign_category
FROM your_table_name

Quick Gotcha #

Your original code uses REGEXP_MATCH, which will fail for campaigns with extra text (like BLU_SUMMER or GRE_CAR). Switching to REGEXP_CONTAINS fixes that, as it looks for the tag substring anywhere in the campaign name.

内容的提问来源于stack exchange，提问作者brjoan