BigQuery字符串字段拆分：括号内多值转多行字段方案咨询

问题分析 #

需要将包含括号内多值的字符串字段拆解为多行，核心难点在于处理类似EXO(BLT7,CFC6,E6D)这类括号内包含多个值的项，需将其拆分为带相同前缀的独立项，再整体拆分多行。

解决方案 #

使用BigQuery的REGEXP_REPLACE配合字符串替换处理括号内的多值，再通过SPLIT和UNNEST生成多行结果：

WITH original_data AS (
  SELECT "CC(2.0)+,EXO(BLT7,CFC6,E6D)" AS column_a UNION ALL
  SELECT "CC(2.0)+,EXO(BPT7,CFC5)+" UNION ALL
  SELECT "MY(22,23,24)+"
)
SELECT
  column_a,
  TRIM(split_item) AS column_b
FROM original_data,
UNNEST(SPLIT(
  -- 核心替换逻辑：将括号内的逗号替换为 ),前缀(，保留后缀的+
  REGEXP_REPLACE(
    column_a,
    r'(\w+)\(([^)]+)\)(\+?)',
    CONCAT(
      '\1(',
      REPLACE('\2', ',', CONCAT('),\1(')),
      ')\3'
    )
  ),
  ','
)) AS split_item

代码解释 #

1. 正则匹配：r'(\w+)\(([^)]+)\)(\+?)'匹配三种分组：
   • \w+：提取项的前缀（如CC、EXO、MY）
   • [^)]+：提取括号内的所有内容（不含右括号）
   • \+?：可选的后缀+（可能存在或不存在）
2. 字符串替换：将括号内的逗号,,替换为),前缀(（如把BLT7,CFC6,E6D变成BLT7),EXO(CFC6),EXO(E6D)），再拼接前缀、括号和后缀，完成单多项的转换。
3. 拆分多行：用SPLIT按逗号拆分转换后的字符串，再通过UNNEST将数组展开为多行。

执行结果 #

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