Oracle中如何将字符串特定部分拆分至单独列？

需求说明 #

• 从Address_1中提取公司名称：仅当字符串不以PO或数字开头时，拆分出开头的公司名称至Company_name列
• 提取包含FLOOR的完整子串（如FIFTH FLOOR）至ADDR_LNE_2_NM，同时将剩余地址部分放入ADDR_LNE_1_NM
• 最终输出需包含Address_1、Address_2、Company_name、ADDR_LNE_1_NM、ADDR_LNE_2_NM列

现有代码问题 #

1. 提取FLOOR相关子串时，仅截取了 FLOOR及之后内容，未包含前面的序数词（如FIFTH、THIRD）
2. 缺少提取公司名称的逻辑实现
3. 存在拼写错误：SUBSRT应为SUBSTR

修正后的完整SQL代码 #

SELECT
    Address_1,
    Address_2,
    -- 提取公司名称：不以PO或数字开头时，拆分出第一个数字/PO之前的部分
    CASE
        WHEN REGEXP_LIKE(Address_1, '^[^0-9PO]') THEN
            TRIM(SUBSTR(Address_1, 1, LEAST(
                NVL(REGEXP_INSTR(Address_1, ' [0-9]'), LENGTH(Address_1)+1),
                NVL(REGEXP_INSTR(Address_1, ' PO '), LENGTH(Address_1)+1)
            ) - 1))
        ELSE NULL
    END AS Company_name,
    -- 拆分地址行1：移除FLOOR相关子串后的内容，同时处理公司名称已拆分的情况
    TRIM(
        REGEXP_REPLACE(
            CASE
                WHEN REGEXP_LIKE(Address_1, '^[^0-9PO]') THEN
                    SUBSTR(Address_1, LEAST(
                        NVL(REGEXP_INSTR(Address_1, ' [0-9]'), LENGTH(Address_1)+1),
                        NVL(REGEXP_INSTR(Address_1, ' PO '), LENGTH(Address_1)+1)
                    ))
                ELSE Address_1
            END,
            '(\w+ FLOOR)$', ''
        )
    ) AS ADDR_LNE_1_NM,
    -- 提取完整的FLOOR子串（包含序数词前缀）
    CASE
        WHEN REGEXP_LIKE(Address_1, '\w+ FLOOR') THEN
            TRIM(REGEXP_SUBSTR(Address_1, '\w+ FLOOR'))
        ELSE NULL
    END AS ADDR_LNE_2_NM
FROM database.table;

代码逻辑解释 #

1. 公司名称提取 #

• 用REGEXP_LIKE(Address_1, '^[^0-9PO]')判断字符串是否不以数字或PO开头
• 通过REGEXP_INSTR定位第一个数字或PO 的位置，截取该位置之前的内容作为公司名称
• 用LEAST函数取两个匹配位置中较靠前的一个，确保正确拆分出公司名称

2. FLOOR子串提取 #

• 用REGEXP_SUBSTR(Address_1, '\w+ FLOOR')匹配包含序数词的完整FLOOR子串
• 用REGEXP_REPLACE从地址中移除该子串，得到ADDR_LNE_1_NM的内容

3. 地址行1处理 #

• 先判断是否已拆分出公司名称，若已拆分则取公司名称之后的剩余内容，否则直接用原Address_1
• 再移除FLOOR相关子串，得到最终的地址行1内容

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