如何在Snowflake中使用正则表达式提取ABC+8位数字的特定模式

需求背景 #

Snowflake表中Column5列存在格式混乱的字符串，需要提取所有符合ABC+8位数字的模式，并将结果以逗号分隔输出。

原始数据示例 #

Column5 :
1) ["[\"( "ABC12345678", "ABC00123451","ABC00543211")\" ]"]
2) ["[\"( ABC87654321\" ]"]

目标输出格式 #

1) ABC12345678,ABC00123451,ABC00543211
2) ABC87654321

正则化解决方案 #

相比多次嵌套REPLACE的方式，使用正则可以精准提取目标模式，避免误处理其他字符。以下是具体SQL实现：

SELECT 
  -- 将所有匹配到的目标字符串用逗号拼接
  LISTAGG(REGEXP_REPLACE(match.value, 'ABC\\s*(\\d{8})', 'ABC\\1'), ',') WITHIN GROUP (ORDER BY match.index) AS column5list
FROM your_table,
  -- 将正则提取的数组转为行数据
  LATERAL FLATTEN(INPUT => REGEXP_SUBSTR_ALL(Column5, 'ABC\\s*\\d{8}', 1, 0, 'i')) AS match

代码说明 #

• REGEXP_SUBSTR_ALL：从Column5中提取所有匹配ABC后接（可选空格）8位数字的子串，'i'参数支持不区分大小写匹配（无需可移除）。
• LATERAL FLATTEN：将正则函数返回的数组结果拆分为多行，方便后续聚合。
• REGEXP_REPLACE：移除ABC与8位数字之间的空格（若原始数据无此类空格可省略此步骤）。
• LISTAGG：将多行匹配结果拼接为逗号分隔的字符串。

优势说明 #

相较于多次嵌套REPLACE的方案，正则化方法更精准：

• 仅提取符合ABC+8位数字规则的内容，不会误处理其他无关字符
• 可灵活适配原始数据中ABC与数字间的空格等小差异
• 代码结构更简洁，可读性更强

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