如何在Athena中筛选匹配指定正则的逗号分隔字符串元素

需求描述 #

Athena表中有一个字符串类型列，值以逗号分隔。例如输入：'ASEIAW,1245555,asda2dd,TPOIBV'，需要得到输出：['ASEIAW,TPOIBV']——即包含所有符合恰好6个大写字母规则元素的数组（元素用逗号拼接后放入数组）。

我尝试过的方法 #

-- 仅提取到第一个匹配项
SELECT REGEXP_EXTRACT('ASEIOW,ASDWQB,TPOIBV,2', '(\b[A-Z]{6,6}\b)+');
-- 输出：ASEIOW

-- 判断字符串是否存在匹配项，返回布尔值
SELECT REGEXP_LIKE('ASEIOW,ASDWQB,TPOIBV,2', '(\b[A-Z]{6,6}\b)+');
-- 输出：true

-- 按匹配内容拆分字符串，得到的是拆分后的剩余部分，不符合需求
SELECT REGEXP_SPLIT('ASEIOW,ASDWQB,TPOIBV,2', '(\b[A-Z]{6,6}\b)+');
-- 输出：['',',',',',',2']

尝试在正则前添加NOT的情况 #

SELECT REGEXP_SPLIT('ASEIOW,ASDWQB,TPOIBV,2', '^(\b[A-Z]{6,6}\b)+');
-- 输出：['',',ASDWQB,TPOIBV,2']

SELECT REGEXP_REPLACE('ASEIOW,ASDWQB,TPOIBV,2', '^(\b[A-Z]{6,6}\b)+');
-- 输出：,ASDWQB,TPOIBV,2

正确解决方案 #

使用REGEXP_EXTRACT_ALL函数提取所有符合规则的元素，再通过ARRAY_JOIN拼接成逗号分隔的字符串，最后放入数组中：

SELECT ARRAY[ARRAY_JOIN(
    REGEXP_EXTRACT_ALL('ASEIAW,1245555,asda2dd,TPOIBV', '(?:^|,)([A-Z]{6})(?:,|$)'),
    ','
)] AS result;

说明 #

1. REGEXP_EXTRACT_ALL：Athena支持的函数，会返回所有匹配正则表达式捕获组的内容，解决了REGEXP_EXTRACT仅提取第一个匹配项的问题。
2. 正则表达式(?:^|,)([A-Z]{6})(?:,|$)：
   • (?:^|,)：非捕获组，匹配字符串开头或逗号，确保匹配的是完整元素的起始边界
   • ([A-Z]{6})：捕获组，精准匹配恰好6个大写字母的内容
   • (?:,|$)：非捕获组，匹配逗号或字符串结尾，确保匹配的是完整元素的结束边界

如果需要直接返回每个匹配元素组成的数组（而非拼接后的字符串数组），可以简化为：

SELECT REGEXP_EXTRACT_ALL('ASEIAW,1245555,asda2dd,TPOIBV', '(?:^|,)([A-Z]{6})(?:,|$)') AS result;

输出为：['ASEIAW', 'TPOIBV']

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