Google BigQuery中使用正则表达式提取指定列表单词时查询无结果的问题排查

Let's break down what's going wrong and how to fix it step by step:

1. Why Your Original Query Fails #

Your regex has two critical issues that prevent it from working in BigQuery:

• Invalid syntax: BigQuery uses RE2 regex and doesn't recognize slash delimiters (/) or inline flags like m inside the pattern string. The trailing .* after $ also makes the pattern impossible to match (since $ marks the end of the string).
• Misplaced capture groups: REGEXP_EXTRACT returns the first capture group by default. Your first group ([\s\S]*?) captures everything before the brand, not the brand itself.

Additionally, if your example string "RR_SM_Brand_A_Additive_Clean_jun2020" doesn't contain the brands listed in your query (Lysol|Airwick|Finish), the regex will return NULL—though the row should still appear unless there's no matching row in your table.

2. Corrected Regex & Query #

To extract the target brand cleanly, simplify your regex to focus only on capturing the brand words. Here's the fixed query:

For Brands Like Brand_A, Brand_B, Brand_C: #

SELECT DISTINCT 
  utm_campaign, 
  REGEXP_EXTRACT(utm_campaign, r'\b(Brand_A|Brand_B|Brand_C)\b') AS extracted_brand
FROM `project.dataset.table`
WHERE utm_campaign = "RR_SM_Brand_A_Additive_Clean_jun2020"

For Brands Like Lysol, Airwick, Finish: #

SELECT DISTINCT 
  utm_campaign, 
  REGEXP_EXTRACT(utm_campaign, r'\b(Lysol|Airwick|Finish)\b') AS extracted_brand
FROM `project.dataset.table`
WHERE utm_campaign = "RR_SM_Brand_A_Additive_Clean_jun2020"

Note: This will return NULL for your example string since it doesn't contain those brands.

3. Key Improvements Explained #

• Word boundaries (\b): Ensures you match whole brand names (avoids partial matches like Brand_A1 if it exists).
• Single capture group: The regex only captures the brand itself, so REGEXP_EXTRACT returns exactly what you need.
• Valid RE2 syntax: No slashes or inline flags—BigQuery interprets this pattern correctly.

4. Additional Tips #

• Case insensitivity: If you need to match regardless of case, convert the string to lowercase first:

  REGEXP_EXTRACT(LOWER(utm_campaign), r'\b(lysol|airwick|finish)\b')
  

• Multiple matches: If a string might contain multiple brands, use REGEXP_EXTRACT_ALL to get an array of matches:

  REGEXP_EXTRACT_ALL(utm_campaign, r'\b(Brand_A|Brand_B|Brand_C)\b') AS extracted_brands
  

• Test first: Verify your regex works with a literal string before running against your table:

  SELECT REGEXP_EXTRACT("RR_SM_Brand_A_Additive_Clean_jun2020", r'\b(Brand_A|Brand_B|Brand_C)\b') AS extracted_brand;
  

This should return Brand_A immediately.

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