Oracle SQL Developer多分隔符拆分姓名列生成扁平化单一新列方法

核心适配规则 #

• 识别两类拆分分隔符：带前后空格的and、逗号（兼容逗号后带空格的场景）
• 拆分后输出为单一NEW_NAME列，无空值、重复值。

方案一：基于现有SUBSTR逻辑改造（适配当前简单数据量） #

你当前写的4列拆分逻辑，核心问题是分隔符不存在时INSTR返回0，会生成空值或重复全量字符串，只需将横向多列转纵向单列，过滤无效值即可。

实现代码 #

-- 新建表存储拆分结果，可先移除建表语句仅运行SELECT验证结果
CREATE TABLE new_name_table AS
WITH raw_split AS (
    SELECT 
        -- 仅当存在逗号时，拆分逗号前后的姓名
        CASE WHEN INSTR(name, ',', 1, 1) > 0 THEN TRIM(SUBSTR(name, 1, INSTR(name, ',', 1, 1) - 1)) END AS split_comma_before,
        CASE WHEN INSTR(name, ',', 1, 1) > 0 THEN TRIM(SUBSTR(name, INSTR(name, ',', 1, 1) + 1, LENGTH(name))) END AS split_comma_after,
        -- 仅当存在`and`分隔符时，拆分前后姓名
        CASE WHEN INSTR(name, ' and ', 1, 1) > 0 THEN TRIM(SUBSTR(name, 1, INSTR(name, ' and ', 1, 1) - 1)) END AS split_and_before,
        CASE WHEN INSTR(name, ' and ', 1, 1) > 0 THEN TRIM(SUBSTR(name, INSTR(name, ' and ', 1, 1) + 1, LENGTH(name))) END AS split_and_after,
        -- 无任何分隔符时，兜底取原始姓名
        CASE WHEN INSTR(name, ',', 1, 1) = 0 AND INSTR(name, ' and ', 1, 1) = 0 THEN TRIM(name) END AS split_raw_only
    FROM original_business_table -- 替换为你的实际原业务表名
)
-- 多列转单列，过滤空值、去重
SELECT DISTINCT split_val AS NEW_NAME
FROM raw_split
UNPIVOT (
    split_val FOR col IN (split_raw_only, split_comma_before, split_comma_after, split_and_before, split_and_after)
)
WHERE split_val IS NOT NULL;

说明：UNPIVOT语法可直接将横向多列转为纵向行，效果与多次UNION ALL完全一致，代码更简洁。

方案二：通用正则拆分方案（适配任意数量姓名场景） #

如果后续业务出现一行包含3个及以上姓名（如A, B and C），原有SUBSTR逻辑无法覆盖，可使用通用正则拆分方案：

实现代码 #

CREATE TABLE new_name_table AS
WITH prep_data AS (
    -- 统一替换两类分隔符为逗号，简化后续拆分逻辑
    SELECT REPLACE(REPLACE(name, ' and ', ','), ', ', ',') AS standardized_name
    FROM original_business_table
),
split_rows AS (
    -- 递归拆分所有逗号分隔的姓名
    SELECT REGEXP_SUBSTR(standardized_name, '[^,]+', 1, LEVEL) AS NEW_NAME
    FROM prep_data
    CONNECT BY LEVEL <= REGEXP_COUNT(standardized_name, ',') + 1
        AND PRIOR SYS_GUID() IS NOT NULL -- 避免递归死循环
        AND PRIOR standardized_name = standardized_name
)
SELECT DISTINCT TRIM(NEW_NAME) AS NEW_NAME
FROM split_rows
WHERE NEW_NAME IS NOT NULL;

最终输出效果 #

执行后生成的new_name_table完全匹配需求：

| NEW_NAME      |
|---------------|
| Joe Smith     |
| John Doe      |
| Jane Doe      |
| Jenny May     |
| Steven Thomas |

内容的提问来源于stack exchange，提问作者ai.jennetta