Oracle PL/SQL循环动态拼接字符串实现地址字段拆分标准化

方案可行性判断 #

你提出的遍历拼接+首字符为数字断点的方案完全可行，完全匹配你给出的葡萄牙语地址结构规律，整体处理准确率可以覆盖绝大多数常规场景，40万行数据量下也可以保证处理效率。
不需要强制使用数组，如果你已经完成了14个独立字段的拆分，直接按字段顺序判断即可，实现逻辑更简单；如果后续字段数量有变动，用数组的方式扩展性更好。

实现代码示例 #

注：以下示例默认你的原始表名为user_address_raw，拆分后的14个字段命名为addr_p1到addr_p14，判断字符串是否以数字开头的逻辑直接使用Oracle自带正则函数REGEXP_LIKE(字段名, '^[0-9]')，你也可以替换成自己写的自定义函数

方式1：直接用SQL查询生成结果（适合快速验证） #

不需要写PL/SQL逻辑，直接用CASE判断即可快速得到拆分结果，方便提前验证准确率：

SELECT 
  -- 拼接街道名
  TRIM(
    CASE WHEN REGEXP_LIKE(addr_p1, '^[0-9]') THEN ''
         WHEN REGEXP_LIKE(addr_p2, '^[0-9]') THEN addr_p1
         WHEN REGEXP_LIKE(addr_p3, '^[0-9]') THEN addr_p1||' '||addr_p2
         WHEN REGEXP_LIKE(addr_p4, '^[0-9]') THEN addr_p1||' '||addr_p2||' '||addr_p3
         WHEN REGEXP_LIKE(addr_p5, '^[0-9]') THEN addr_p1||' '||addr_p2||' '||addr_p3||' '||addr_p4
         WHEN REGEXP_LIKE(addr_p6, '^[0-9]') THEN addr_p1||' '||addr_p2||' '||addr_p3||' '||addr_p4||' '||addr_p5
         ELSE addr_p1||' '||addr_p2||' '||addr_p3||' '||addr_p4||' '||addr_p5||' '||addr_p6
    END
  ) AS street_name,
  -- 提取门牌号
  TRIM(
    CASE WHEN REGEXP_LIKE(addr_p1, '^[0-9]') THEN addr_p1
         WHEN REGEXP_LIKE(addr_p2, '^[0-9]') THEN addr_p2
         WHEN REGEXP_LIKE(addr_p3, '^[0-9]') THEN addr_p3
         WHEN REGEXP_LIKE(addr_p4, '^[0-9]') THEN addr_p4
         WHEN REGEXP_LIKE(addr_p5, '^[0-9]') THEN addr_p5
         WHEN REGEXP_LIKE(addr_p6, '^[0-9]') THEN addr_p6
         ELSE NULL
    END
  ) AS house_number,
  -- 提取补充参考信息
  TRIM(
    CASE WHEN REGEXP_LIKE(addr_p1, '^[0-9]') THEN addr_p2||' '||addr_p3||' '||addr_p4||' '||addr_p5||' '||addr_p6||' '||addr_p7||' '||addr_p8||' '||addr_p9||' '||addr_p10||' '||addr_p11||' '||addr_p12||' '||addr_p13||' '||addr_p14
         WHEN REGEXP_LIKE(addr_p2, '^[0-9]') THEN addr_p3||' '||addr_p4||' '||addr_p5||' '||addr_p6||' '||addr_p7||' '||addr_p8||' '||addr_p9||' '||addr_p10||' '||addr_p11||' '||addr_p12||' '||addr_p13||' '||addr_p14
         WHEN REGEXP_LIKE(addr_p3, '^[0-9]') THEN addr_p4||' '||addr_p5||' '||addr_p6||' '||addr_p7||' '||addr_p8||' '||addr_p9||' '||addr_p10||' '||addr_p11||' '||addr_p12||' '||addr_p13||' '||addr_p14
         WHEN REGEXP_LIKE(addr_p4, '^[0-9]') THEN addr_p5||' '||addr_p6||' '||addr_p7||' '||addr_p8||' '||addr_p9||' '||addr_p10||' '||addr_p11||' '||addr_p12||' '||addr_p13||' '||addr_p14
         WHEN REGEXP_LIKE(addr_p5, '^[0-9]') THEN addr_p6||' '||addr_p7||' '||addr_p8||' '||addr_p9||' '||addr_p10||' '||addr_p11||' '||addr_p12||' '||addr_p13||' '||addr_p14
         WHEN REGEXP_LIKE(addr_p6, '^[0-9]') THEN addr_p7||' '||addr_p8||' '||addr_p9||' '||addr_p10||' '||addr_p11||' '||addr_p12||' '||addr_p13||' '||addr_p14
         ELSE addr_p7||' '||addr_p8||' '||addr_p9||' '||addr_p10||' '||addr_p11||' '||addr_p12||' '||addr_p13||' '||addr_p14
    END
  ) AS extra_info
FROM user_address_raw;

方式2：PL/SQL批量处理（适合全量数据落地） #

如果需要批量处理全表数据，用数组循环的方式逻辑更简洁，后续调整字段数量也更方便：

DECLARE
  -- 定义存储14个地址片段的数组
  TYPE addr_arr IS VARRAY(14) OF VARCHAR2(200);
  v_addrs addr_arr;
  v_street_name VARCHAR2(1000);
  v_house_number VARCHAR2(200);
  v_extra_info VARCHAR2(2000);
  v_break_pos NUMBER := 0;
  v_count NUMBER := 0;
BEGIN
  -- 遍历所有地址记录
  FOR rec IN (SELECT id, addr_p1, addr_p2, addr_p3, addr_p4, addr_p5, addr_p6, addr_p7, addr_p8, addr_p9, addr_p10, addr_p11, addr_p12, addr_p13, addr_p14 FROM user_address_raw) LOOP
    -- 把拆分字段装入数组
    v_addrs := addr_arr(rec.addr_p1, rec.addr_p2, rec.addr_p3, rec.addr_p4, rec.addr_p5, rec.addr_p6, rec.addr_p7, rec.addr_p8, rec.addr_p9, rec.addr_p10, rec.addr_p11, rec.addr_p12, rec.addr_p13, rec.addr_p14);
    v_street_name := '';
    v_house_number := NULL;
    v_extra_info := '';
    v_break_pos := 0;
    
    -- 循环找第一个以数字开头的字段作为断点
    FOR i IN 1..v_addrs.COUNT LOOP
      IF v_addrs(i) IS NOT NULL AND REGEXP_LIKE(v_addrs(i), '^[0-9]') THEN
        v_break_pos := i;
        v_house_number := v_addrs(i);
        EXIT;
      END IF;
      v_street_name := v_street_name || ' ' || v_addrs(i);
    END LOOP;
    
    -- 拼接剩余的补充信息
    IF v_break_pos > 0 AND v_break_pos < v_addrs.COUNT THEN
      FOR i IN v_break_pos+1..v_addrs.COUNT LOOP
        v_extra_info := v_extra_info || ' ' || v_addrs(i);
      END LOOP;
    END IF;
    
    -- 结果更新到结果表，需提前建好address_parsed表，字段为id、street_name、house_number、extra_info
    INSERT INTO address_parsed(id, street_name, house_number, extra_info)
    VALUES(rec.id, TRIM(v_street_name), v_house_number, TRIM(v_extra_info));
    
    -- 每1万条提交一次，避免事务过大
    v_count := v_count + 1;
    IF MOD(v_count, 10000) = 0 THEN
      COMMIT;
    END IF;
  END LOOP;
  COMMIT;
END;
/

优化建议 #

• 正式跑全量数据前，先抽取1000-2000条样本验证拆分准确率，针对少量异常场景（如街道名本身包含数字、门牌号出现在第6位之后）补充特殊规则
• 对于拆分后门牌号为空的记录，可以单独筛选出来人工校验，补全规则

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