Snowflake正则拆分列转表结果异常，请求技术协助

场景与问题 #

使用Snowflake查询拆分表中字符串列到多列，但结果不符合预期，具体如下：

输入示例 #

Activity type [DP - mcr modifyand quac endo; bio fert]; PharmSon BT acticity code [AYx765]

预期输出 #

Activity type 在column1，DP - mcr modifyand quac endo; bio fert 在column2
PharmSon BT acticity code 在column1，AYx765 在column2

实际输出 #

Activity type 在column1--column2为空
bio fert] 在column1--column2为空
PharmSon BT acticity code 在column1--AYx765 在column2

当前使用的查询语句 #

WITH parsed_data AS (
    -- Split FILATT data by semicolons and flatten the array into rows
    SELECT
        INTRNL AS EQ_INTRNL,
        'DEV' AS ENVIRONMENT,
        FILATT AS raw_data, 
        SPLIT(FILATT, ';') AS data_array -- Split the data by ';'
    FROM table1
),

exploded_data AS (
    -- Flatten the array so each item is in a separate row
    SELECT
        EQ_INTRNL,
        ENVIRONMENT,
        raw_data,
        TRIM(value) AS part, -- Each value is now in 'part'
        ROW_NUMBER() OVER (PARTITION BY EQ_INTRNL ORDER BY CURRENT_TIMESTAMP()) AS column1 -- Assign a sequential number, reset for each EQ_INTRNL
    FROM parsed_data,
    LATERAL FLATTEN(input => data_array)
),

extracted_columns AS (
    SELECT
        EQ_INTRNL,
        ENVIRONMENT,
        column1, -- The sequential number for the row
        -- Extract the identifier part (before '[')
        REGEXP_SUBSTR(part, '^[^\\[]+') AS column2,
        -- Extract the content inside brackets, excluding the brackets themselves
        REGEXP_SUBSTR(part, '\\[([^\\]]+)\\]', 1, 1, 'e') AS column3
    FROM exploded_data
)

SELECT
    EQ_INTRNL,
    column2,
    column3,
    ENVIRONMENT,
    column1
FROM extracted_columns 
WHERE column3 !=''
ORDER BY EQ_INTRNL, column1;

问题根源 #

原查询错误地使用分号;作为全局分隔符，但目标数据中括号内包含分号（属于内容的一部分），导致拆分位置错误，将单个完整条目拆成了多段，进而提取失败。

修正方案 #

改用]; 作为顶级分隔符（这是两个完整条目之间的实际分隔标记），避免拆分括号内的内容，同时调整顺序排序逻辑保证稳定性。

修正后的查询语句 #

WITH parsed_data AS (
    -- 按顶级分隔符']; '拆分，仅分割独立条目，不破坏括号内内容
    SELECT
        INTRNL AS EQ_INTRNL,
        'DEV' AS ENVIRONMENT,
        FILATT AS raw_data, 
        REGEXP_SPLIT_TO_ARRAY(FILATT, '];\\s*') AS data_array 
    FROM table1
),

exploded_data AS (
    -- 展开数组，用FLATTEN的ordinal字段保持原始顺序（替代不稳定的CURRENT_TIMESTAMP）
    SELECT
        EQ_INTRNL,
        ENVIRONMENT,
        raw_data,
        -- 移除条目末尾可能残留的']'，保证正则提取一致性
        TRIM(REGEXP_REPLACE(value, '\\]$', '')) AS part,
        ordinal AS column1
    FROM parsed_data,
    LATERAL FLATTEN(input => data_array)
),

extracted_columns AS (
    SELECT
        EQ_INTRNL,
        ENVIRONMENT,
        column1,
        -- 提取[之前的标识部分并去除多余空格
        TRIM(REGEXP_SUBSTR(part, '^[^\\[]+')) AS column2,
        -- 提取括号内的核心内容
        REGEXP_SUBSTR(part, '\\[([^\\]]+)\\]', 1, 1, 'e') AS column3
    FROM exploded_data
)

SELECT
    EQ_INTRNL,
    column2,
    column3,
    ENVIRONMENT,
    column1
FROM extracted_columns 
WHERE column3 IS NOT NULL AND column3 != ''
ORDER BY EQ_INTRNL, column1;

关键调整说明 #

1. 拆分规则优化：用REGEXP_SPLIT_TO_ARRAY(FILATT, '];\\s*')替代原SPLIT(FILATT, ';')，仅识别条目间的]; 分隔符，忽略括号内的分号。
2. 顺序稳定性：使用FLATTEN自带的ordinal字段作为行号，替代CURRENT_TIMESTAMP()，保证拆分后的顺序与原始字符串一致。
3. 条目清理：用REGEXP_REPLACE(value, '\\]$', '')处理最后一个条目末尾的]，确保正则提取逻辑统一。

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