如何在Snowflake中返回所有正则表达式匹配组？

首先明确：大部分原生SQL的REGEXP_SUBSTR确实无法一次性返回所有捕获组，尤其是当捕获组数量动态变化时，原生函数的局限性很明显。

固定数量捕获组的替代方案 #

如果你的捕获组数量是固定的，不用重复调用REGEXP_SUBSTR，可以用一些方言特定的技巧：

• PostgreSQL：直接用REGEXP_MATCHES函数，它会返回一个包含所有捕获组的数组：

  SELECT REGEXP_MATCHES('abc-!@#_123', '^(abc)-(!@#)_(123)$', 'ie');
  -- 返回结果：{"abc", "!@#", "123"}
  

• MySQL 8.0+/MariaDB：可以用REGEXP_REPLACE把捕获组拼接成JSON格式，再转成JSON数组：

  -- 简洁拼接后转JSON
  SELECT JSON_PARSE(REGEXP_REPLACE('abc-!@#_123', '^(abc)-(!@#)_(123)$', '["$1","$2","$3"]'));
  

• BigQuery：用STRUCT配合REGEXP_EXTRACT一次性提取多个固定组：

  SELECT STRUCT(
    REGEXP_EXTRACT('abc-!@#_123', '^(abc)-(!@#)_(123)$', 1) AS part1,
    REGEXP_EXTRACT('abc-!@#_123', '^(abc)-(!@#)_(123)$', 2) AS part2,
    REGEXP_EXTRACT('abc-!@#_123', '^(abc)-(!@#)_(123)$', 3) AS part3
  );
  

这些方案都依赖固定组数量，需要提前明确每个组的位置。

动态数量捕获组的痛点 #

像你例子里的^(abc)-(!@#)(_\\d+)+$，重复捕获组(_\\d+)只会保留最后一次匹配结果（也就是_456），这是正则引擎的特性——重复捕获组不会累积所有匹配项，只会覆盖为最后一次。这种情况下，原生SQL函数几乎没有办法直接获取所有重复的捕获组结果。

终极解决方案：UDF #

如果你的场景是动态数量捕获组，或者想要通用的一次性提取所有组的逻辑，自定义函数（UDF）是最可靠的选择：

• 用Python、JavaScript等语言编写UDF，利用语言本身的正则引擎（比如Python的re.match.groups()或re.findall）提取所有捕获组，包括重复组的所有匹配项。
• 比如Python UDF示例：

  import re
  def extract_all_groups(text, pattern):
      match = re.match(pattern, text)
      if not match:
          return []
      
      # 区分普通组和重复组
      groups = list(match.groups())
      # 检查是否存在重复捕获组
      if '+' in pattern or '*' in pattern:
          # 提取重复部分的所有匹配
          repeat_pattern = re.findall(r'\((.*?)\+\)', pattern)[0]
          repeat_matches = re.findall(repeat_pattern, text)
          # 合并普通组和重复组结果
          return groups[:-1] + repeat_matches
      return groups
  

  将这个UDF注册到你的SQL环境中，就能一次性返回所有捕获组，包括动态重复的组。

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