如何在Snowflake中将非结构化地址拆分为多行数据

核心问题在于你之前的写法直接拆分整个地址字符串，没先把门牌号组和街道信息分离，导致拆分后门牌号无法对应街道。以下是覆盖所有格式的分步解决方案：

步骤1：分离门牌号组与街道信息 #

先用正则把地址拆为两部分——开头的门牌号组合（支持逗号、空格、and连接格式），以及剩余的街道信息：

WITH split_addr AS (
    SELECT 
        col_A,
        -- 匹配开头的门牌号组（包含数字、逗号、空格、and、连接符-）
        REGEXP_SUBSTR(col_A, '^[0-9,\\s-]+(?:and\\s[0-9,\\s-]+)?') AS num_group,
        -- 提取街道部分：移除开头门牌号组后清理多余空格
        TRIM(REGEXP_REPLACE(col_A, '^[0-9,\\s-]+(?:and\\s[0-9,\\s-]+)?', '')) AS street
    FROM your_table
)

步骤2：统一分隔符并拆分门牌号 #

将门牌号组里的空格、and统一替换为逗号，再用LATERAL SPLIT_TO_TABLE拆分单个门牌号：

, split_nums AS (
    SELECT 
        col_A,
        street,
        TRIM(s.value) AS house_num
    FROM split_addr,
         LATERAL SPLIT_TO_TABLE(
             -- 把空格、and替换成逗号，统一拆分规则
             REGEXP_REPLACE(num_group, '\\s+and\\s+|\\s+', ','),
             ','
         ) s
    WHERE TRIM(s.value) != '' -- 过滤替换后产生的空值
)

步骤3：处理门牌号范围（可选） #

如果存在4400-4402这类范围格式，用GENERATOR函数展开为单个门牌号：

, expand_ranges AS (
    SELECT 
        col_A,
        street,
        CASE 
            WHEN house_num LIKE '%-%' THEN 
                (SELECT seq4() + SPLIT_PART(house_num, '-', 1)::INT
                 FROM TABLE(GENERATOR(ROWCOUNT => (SPLIT_PART(house_num, '-', 2)::INT - SPLIT_PART(house_num, '-', 1)::INT + 1))))
            ELSE house_num::INT
        END AS house_num
    FROM split_nums
)

最终生成规范地址 #

拼接门牌号与街道，输出拆分后的结果：

SELECT 
    col_A AS original_addr,
    house_num || ' ' || street AS normalized_addr
FROM expand_ranges
ORDER BY original_addr, house_num;

效果验证 #

针对你给出的示例4402, 4420, 4330, 4502 hecson Blvd SW，会输出4行规范地址：

• 4402 hecson Blvd SW
• 4420 hecson Blvd SW
• 4330 hecson Blvd SW
• 4502 hecson Blvd SW

对于4400 and 4402 hecson Blvd SW、4400 4402 hecson Blvd SW这类格式，会正确拆分为2行；4400-4402 hecson Blvd SW则会展开为3行。

你之前代码的问题 #

之前直接用逗号拆分整个col_A，会把街道部分（比如示例里的4502 hecson Blvd SW）也当成拆分项，且没有单独提取街道信息，导致门牌号无法和街道对应关联。

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