如何用Snowflake SQL正则生成字符串的连续迭代字符对

由于Snowflake仅支持POSIX基础/扩展正则，不支持前瞻、后顾及递归语法，靠正则无法实现重叠式的连续字符对提取，必须结合SQL字符串函数与行生成逻辑来解决。

核心思路 #

对于长度为len的字符串，我们需要生成len-1个连续双字符对，每个对对应字符串第i位与第i+1位的字符（i从1到len-1）。具体步骤：

• 计算目标字符串的长度
• 生成从1到len-1的数字序列
• 对每个数字，截取对应位置的双字符
• 可选：将结果聚合为数组格式

示例代码 #

处理单个字符串（以"FOOBARBAZ"为例）

WITH input_str AS (
    SELECT 'FOOBARBAZ' AS str
),
char_positions AS (
    SELECT 
        ROW_NUMBER() OVER () AS pos
    FROM input_str,
    TABLE(GENERATOR(ROWCOUNT => (SELECT LENGTH(str)-1 FROM input_str)))
)
SELECT 
    ARRAY_AGG(SUBSTRING(str, pos, 2)) AS char_pairs
FROM input_str, char_positions;

执行后返回结果：["FO", "OO", "OB", "BA", "AR", "RB", "BA", "AZ"]

处理多行数据（包含"FOOBARBAZ"和"FRANCE"）

CREATE OR REPLACE TEMP TABLE test_strings (
    id INT,
    str VARCHAR
);
INSERT INTO test_strings VALUES
(1, 'FOOBARBAZ'),
(2, 'FRANCE');

WITH char_positions AS (
    SELECT 
        id,
        ROW_NUMBER() OVER (PARTITION BY id) AS pos
    FROM test_strings,
    TABLE(GENERATOR(ROWCOUNT => 100)) -- 设足够大的数值覆盖最长字符串的长度-1
    WHERE pos <= LENGTH(str)-1
)
SELECT 
    id,
    str,
    ARRAY_AGG(SUBSTRING(str, pos, 2)) AS char_pairs
FROM test_strings
JOIN char_positions USING(id)
GROUP BY id, str;

执行后返回结果：

关键说明 #

• GENERATOR(ROWCOUNT => ...)用于生成足够行数，若不确定最长字符串长度，可设较大数值（如100），再通过WHERE pos <= LENGTH(str)-1过滤多余行。
• SUBSTRING(str, pos, 2)从第pos位开始截取2个字符，恰好得到连续双字符对。
• 若不需要数组格式，去掉ARRAY_AGG()即可得到每行一个字符对的结果。

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